Excel drop-down values versus labels

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Tresman - 09 Dec 2004 14:53 GMT

I can't figure out how to duplicate Web-based Select boxes, as drop-down
lists in Excel.

I can create the drop-down list using Data | Validation | Settings,
selecting from Allow the opion List (Ignore Blank and In-Cell dropdown
checked), and then enter into Source: Standard, Large, Extra Large (each
item separated by commas).

But I can't figure out how to calculate which item is selected without
duplicating the data, It seems inefficient to duplicate "Standard, Large,
Extra Large" elsewhere, and do a lookup.

1. Is there not a function which will return the Nth item selected from this
short list?

2. Or can I assign the values 5, 10, 20 to each of the items that appears
in the list?

Regards,
Ian Tresman
Derby, UK

Reply to this Message

Frank Kabel - 09 Dec 2004 18:28 GMT

Hi
I would do it the following way:
1. Set up a table with your list values and the associated values. e.g.
A B
1 Standard 1
2 Large 5
3 ...

2. In the data validation use
=A1:A10
as source for your list

3. Use VLOOKUP to get the associated value. lets say your drop down is
in C1 then use
=VLOOKUP(C1,A1:B10,2,0)

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> I can't figure out how to duplicate Web-based Select boxes, as drop-down
> lists in Excel.
[quoted text clipped - 17 lines]
> Ian Tresman
> Derby, UK

Reply to this Message