Building a drop down spreadsheet in excel

[frankelfish]

Hey guys.

My excel skills are so poor and I am getting so stressed that I hope someone could help me here. Its so simple yet i cant do it.

I will try and explain it to the best I can.

I want to create an Excel sheet with dropdowns.

So for example I would like to add data into these dropdowns...

For example. dropdowns-

Type of animal; cats, dogs, rabbits

Age; 0,1,2,3,4,5 etc

Weight; under 2kg, over 2kg

Color; black, white, other


I think you understand. But what I want it to do is once I have picked all my drop downs I can add data into that dropdown.

So the next time I click that exact combination of dropdowns it brings up data i have added for that.

I would like to be able to add as many dropdowns as possible if needed.


To someone with experience this probably very very simple.

I have bought a couple of excel learning books and after 15 months I still cant grasp any coding aspects of it.

Thank you in advance

Scott

 

[alansidman]

Here is a link on cascading/dependent combo boxes

Cascading Combo Boxes in Excel | The Office Corner

 

[Kamolga]

Hi Scott,

I see only two ways to have variable dropdown lists:

The first and most complicated is to use macro's. That is to be used only if the names to be add can com from everywhere. Note that my way to set up such macro's would be to send copy-paste the added names in the tables of the second method or userforms depending on the cases.

The second method is based on "table". I usually build a sheet called "tables" where I have all my tables. The sheet is hidden and protected if needed. On that sheet I would have a 1 column Table for animals.

-Title a1: Type of animal. A2 cats a3 dogs a4 rabits. I would then select a1 to a4, click insert menu and table. Select my table has headers.
-then select a2-a4, click formulas sheet and define name and call it Listanimal. Note that your name will refer to =Table1[Type of animal] automatically if you have not named the table differently.

-Now if you build a dropdown menu referring to =Listanimal, you will have the 3 animals. If you put another animal in a5, the table will extend and it ill be in your list.


Voilà! Hope it helps