Mask Drop Down List values
 

Hi all,

Just a quick question, is it possible to mask the values in a
validation drop down list?

I'm using the indirect function in many of my formulas and wish the
user to select the appropriate cell reference from a drop down list.

Thus A-Z is placed into formulas using INDIRECT however I would rather
more descriptive values in my drop down.

Is this possible?

Cheers


--
DangerMouse
------------------------------------------------------------------------
DangerMouse's Profile: http://www.excelforum.com/member.php...o&userid=27755
View this thread: http://www.excelforum.com/showthread...hreadid=550961

Mask Drop Down List values
 

Hi

Create an additional sheet, p.e. MyList. On this sheet create a table like:
Description, Char
"First selection" "A"
"Second selection" "B"
....
"Last selection" "Z"

Define named ranges
DescrList=OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$ A)-1,1)
MyList=OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,2)

Use named range DescrList as source for your data validation list, i.e.
=DescrList

Create named values (I assume the dropdown is p.e. in cell Sheet1!B1)
MySelection=Sheet1!$B$1
MyChar=VLOOKUP(MySelection,MyList,2,0)

Everywhere in your formulas you have to refer to MyChar.
To avoid some confusion, it'll be best to hide the sheet MyList - no need
for an user to see it.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"DangerMouse"
wrote in message
...

Hi all,

Just a quick question, is it possible to mask the values in a
validation drop down list?

I'm using the indirect function in many of my formulas and wish the
user to select the appropriate cell reference from a drop down list.

Thus A-Z is placed into formulas using INDIRECT however I would rather
more descriptive values in my drop down.

Is this possible?

Cheers


--
DangerMouse
------------------------------------------------------------------------
DangerMouse's Profile:
http://www.excelforum.com/member.php...o&userid=27755
View this thread: http://www.excelforum.com/showthread...hreadid=550961