Computer bod of the org- I need your help
 

As the title says am bit stumped on how to do a excel formula (its about 5 years since I did them properly).

I'm needing it to be a idiot proof process (Ideally automagic) for a automatic ID generator.

It needs to add an ID no (in this case: "Building Name: xxx") when the name boxes are filled (or Surname).
I would do it in access but the company doesn't use it outside of HR and finance.


Many thanks in advance.
Andy

Re: Computer bod of the org- I need your help
 

Sounds like you need a simple "add together" formula. If it's a combination of text and name say (where the name is entered in another cell) and the text is constant use

=concatenate("text: ",b2)

If cell b2 has the word flange in it the result would be

text: flange

If the text is variable then slimply link two or more fields in exactly the same way, such as

=concatenate(c1,d1,e1)

That way you can set up a table and join two or more fields to get a single ID/reference if that's what you need.


Does that help?

Re: Computer bod of the org- I need your help
 

Yep it does Trev, makes sense of the one I got to work earlier

Is there a way to set it up so the ID code isn't present in every cell when its exported?
As the database its for will pick up 100,000 entries instead of the 500 needed

Re: Computer bod of the org- I need your help
 

I'm a bit rusty with Excel, but you'd probably want to only generate the ID if there was an entry in the name box. Something along the lines of:

=IF(ISBLANK(B1), "", CONCATENATE("text: ", B1))

Re: Computer bod of the org- I need your help
 

Well its 80% there, just need to get false to show as blank
Cheers lads so far

Re: Computer bod of the org- I need your help
 

Righto
Just need to get the ISBLANK TO WORK

This is where I am at:
=IF(ISTEST(A2,(CONCANTENATE("MAINGATE",LEFT("00000 0",6-LEN(ROW(A2)-1)),ROW(A2)-1) [It works perfectly to this point] ,(ISBLANK(A2),(CONCATENATE("",LEFT(ROW(A2)-1)))) This is where its gone pete tong and not working

Re: Computer bod of the org- I need your help
 

A bit of a guess because I can't see the overall picture but it looks like you need an additional IF clause on the last part where the ISBLANK condition is (the bit that's gone pete tong). Maybe something like this?

=IF(ISTEXT(A2,CONCANTENATE("MAINGATE",LEFT("000000 ",6-LEN(ROW(A2)-1)),ROW(A2)-1),IF(ISBLANK(A2),CONCATENATE("",LEFT(ROW(A2)-1)),"")))

Re: Computer bod of the org- I need your help
 

And theres me having probalme getting vlookup to work...which i did in the end!

Re: Computer bod of the org- I need your help
 

makes VLookup look easy
Daft thing is I had to design a POS system using excel as part of an AS level, and that had about 90 pages of formula and script to it

Re: Computer bod of the org- I need your help
 

Update time.

I've got the if false to work displaying "TRUE" so I'm somewhere

=IF(ISTEST(A2,(CONCANTENATE("MAINGATE",LEFT("00000 0",6-LEN(ROW(A2)-1)),ROW(A2)-1),(OR(A2="")))

Just need to work out the show blank after this point :smt017