Solved: Change Values

[Blackie50]

Hi,

Can anyone help with following

I have a number of short codes in a drop down list on sheet1 and would like the long code to be displayed on sheet2 in a related cell e.g.

A1 (sheet1) = Hi (short) on A1 (sheet2) Hello (Long)
A1 (sheet1) = Lo (Short) on A1 (sheet2) Lollipop (Long)

Also is it possible to have the dropdown list display all possible values instead of having to scroll down?

many thanks
Jon

[Bob Phillips]

Create a table of short and long codes, and use VLOOKUP to lookup the other value.

[Blackie50]

Ok - managed to create Lookup table.

Any ideas on the dropdown list extension?

Also would like to convert a cell containing

NNNNNNNLN

in to 14/NNNNNNN-N/L

where the L & N in the first string are the N/L in the second.

Again the first string is on sheet1 and the second on sheet2

any ideas welcome

thanks again
Jon

[Bob Phillips]

Ok - managed to create Lookup table.

Any ideas on the dropdown list extension?

If it is data validation, you cannot. If it is a combobox from the Controls toolbar, it is just a property.

Also would like to convert a cell containing

NNNNNNNLN

in to 14/NNNNNNN-N/L

where the L & N in the first string are the N/L in the second.

Again the first string is on sheet1 and the second on sheet2

="14/"&LEFT(Sheet1!A1,8)&"-"&RIGHT(Sheet1!A1,1)&"/"&MID(Sheet1!A1,LEN(Sheet1!A1)-1,1)

[Blackie50]

thanks for your help

Jon