MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Drop down box


Posted by Jay on January 16, 2002 9:42 PM

I need to create a drop down box that will when clicked on will have choices when one is chosen will return several values to the active cell the list box is located. What I am choosing from is a simple list with four colomns.
Name Type Color Cost

Charlotte Rose Red 1.50
Gerbera Daisey Pink 2.50


Can I just choose one name and the other three columns will be chosen also? Or do they need to be linked together. Help Jay


Posted by Donald on January 17, 2002 7:20 AM

If I understand your question correctly, you want to choose the name and have the type, color, and cost display. An easy way to do that is to use a an if and a lookup to relate them. Assuming you have all possibilities in a table V1:Z2 (using your example) and you choose from a pull-down in A1, this formula in B1 will display the type (and also prevent annoying error messages when no selection is made, so you can copy down and leave it alone):

=IF(ISNA(VLOOKUP($A1,$V$1:$Z$2,2,FALSE)),"",VLOOKUP($A1,$V$1:$Z$2,2,FALSE))

HTH,
Donald