Dropdown list with named cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WaterDog - 18 Nov 2005 02:43 GMT

Hi,

I would like to set up a drop down list where the user sees text descriptors
but the value of the cell is a numerical value.

Say I have three possible types of wages that I have placed into the named
range "rate":

regular
overtime
doubletime

For each rate type there is an associated value:
regular => 5.00
overtime => 7.50
doubletime => 10.00

Now I'd like a drop down list that allows the user to from select regular,
overtime, or double time as a text string. Then the value of the cell
holding the validation list takes on the numeric value 5,7.5, or 10
respectively while still showing the text descriptor. That value could then
be used in calculations in other cells. The user would see the text
description but other cells would see the numeric value associated with the
text descriptor. Any way to do this?

Reply to this Message

Rowan Drummond - 18 Nov 2005 03:08 GMT

I would set up the rate types and values in a table (perhaps on a hidden
sheet) just as you have shown them. Have your rate types in a drop down
and in another cell (again hidden if you like) use a vlookup to return
the corresponding numerical value. That (hidden) cell can then by used
in your formulae.

Hope this helps
Rowan

> Hi,
>
[quoted text clipped - 20 lines]
> description but other cells would see the numeric value associated with the
> text descriptor. Any way to do this?

Reply to this Message