Shortcut to long entry??

[afp5061]

Hey everybody,

Please be aware that before I start any thread I search to my best ability to find the solution. If I start a thread, its because I had difficulties trying to find exactly what I need.

Anyways, I am working on an excel sheet where I have 5 different locations items ship to. They are written by geographic location (digits only). What I am trying to do is to have a column formatted to enter a digit and have the geographic location appear. For example:

If a user enters the number "5" on a cell the following will appear on the user hits enter: 01-005-00-744-8639
If a user enters the number "6" on a cell the following will appear on the user hits enter: 01-006-00-744-8639
If a user enters the number "9" on a cell the following will appear on the user hits enter: 01-009-00-744-8639
and so on...

This function will only be available in 1 column.

Thank you so much.

[knitterkuba]

If you add a column you could have a simple ="01-00"&"X1&"-00-744-8639" assuming that X is the column where you enter the number. Other than that you would be looking at inserting vba code.

[afp5061]

If you add a column you could have a simple ="01-00"&"X1&"-00-744-8639" assuming that X is the column where you enter the number. Other than that you would be looking at inserting vba code.

I tried this but it's not working? Could you elaborate a little more?

Thanks

[oeldere]

Please Login or Register  to view this content.

x1 = number 5 or 6 or 9.

y1 is the formula above.

the result (in y1) is like you asked.

[afp5061]

yeah I understood that, but after playing around I found the mistake. Knitter typed this ="01-00"&"X1&"-00-744-8639" but I think he meant this: ="01-00"&X1&"-00-744-8639". Removing the quotation and leave it as "&X1&" worked for me. Thank you!!

[newdoverman]

You could use a simple VLOOKUP table to accomplish this.

	X	Y	Z	AA	AB	AC	AD
1	6	01-006-00-744-8639				5	01-005-00-744-8639
2	5	01-005-00-744-8639				6	01-006-00-744-8639
3	9	01-009-00-744-8639				9	01-009-00-744-8639
4
5			=IF(X1="","",VLOOKUP(X1,$AC$1:$AD$3,2,0))