Computer bod of the org- I need your help

[andrewsmith]

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

[Sir Trev]

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?

[andrewsmith]

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

Originally Posted by andrewsmith

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

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))

[andrewsmith]

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

[andrewsmith]

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

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)),"")))

[Viney]

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

[andrewsmith]

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

[andrewsmith]

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