Building a drop down spreadsheet in excel

frankelfish

New Member
Joined
Jan 24, 2016
Messages
1
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,180
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
 
Last edited:

Forum statistics

Threads
1,147,451
Messages
5,741,195
Members
423,647
Latest member
lyanndominique

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top