MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Drop Down Boxes/Lists


Posted by Rick on March 26, 2001 1:29 PM

I can't figure this out!!!! Is there any way for
me to create some drop down boxes in Excel. What I
want to do is have a box drop down with different
food choices (Like apple, banana etc) and when I choose
one I want it to show some values like calories, fat
etc. Is there any wau to do this and if so what is the
best way?
Thanks so much in advance!!!!


Posted by mseyf on March 26, 2001 2:40 PM

the easiest way go to the menu bar and select Data > Validation
in the 'Allow' box select 'List'
in the 'Source' box, either reference a range that contains a list of your allowable choices or just type in the choices separated by commas ("" are not necessary).
play with the other tabs to suit your needs

HTH

Mark

Posted by Ian on March 26, 2001 2:41 PM

Since no one has responded with any flashy ways, I'll try to get you started with one way. I'm not sure exactly end result you want, but I'll assume you want your user to enter the fruit in a drop down in cell A2 and list calories and grams of fat in B2 and C2. First you need to enter the data you want in a table that can be hidden later, lets say in J1 through Jxx you list fruit, K1..Kxx calories, and L1..lxx you list fat. Now sort this list alphabetically by column J.

Go to cell A2 and select valadation from the data menu. Under allow, change this to list and highlight your list in column J under the source box. You can also add some creative Input and error messages here. You have your drop down list that allows only those values

You can use vertical look up to look up calories and fat. In B2 enter
=IF(A2<>"",VLOOKUP($A2,$J$1:$L$xx,2,FALSE),"") where $L$xx is the last cell of your
In C2=
=IF(A2<>"",VLOOKUP($A2,$J$1:$L$xx3,3,FALSE),"")

I'll end this with my typical caveat to check back, as I'm sure their are probably better ways.

Good luck

Posted by Rick on March 26, 2001 7:13 PM

Thanks VERY MUCH for the reply Ian, it works GREAT!!!

Posted by Rick on March 28, 2001 1:01 PM