PDA

View Full Version : Solved: Change Values



Blackie50
04-13-2010, 04:45 AM
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
04-13-2010, 05:15 AM
Create a table of short and long codes, and use VLOOKUP to lookup the other value.

Blackie50
04-13-2010, 06:34 AM
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
04-13-2010, 08:00 AM
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
04-16-2010, 12:51 AM
thanks for your help

Jon