Show a range off a drop down menu

Solved/Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,
What I'm trying to do is make a drop down menu that when you pick an option from it, the column next to it changes to a list corresponding to what you picked. The amount of things on the list varies from one thing to another.

Example:
You pick Hamburgers so it lists:
Hamburger
Cheeseburger
Turkey burger

Or you pick Pizza so it lists:
Cheese
Pepperoni
Meat lovers
Vegetarian


1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Brazell,

To do this you need to create 3 lists:

List 1:
Hamburgers
Pizza

List 2:
Hamburger
Cheeseburger
Turkey burger

List 3:
Cheese
Pepperoni
Meat lovers
Vegetarian

Now select list 2. Now look above the letter A of column A. It will show you the top cell of your selected list (If the range of your list is A3:A5, it will show A3.).
Change it to Hamburgers.

Do the same for list 3 but now name it Pizza.

Now on to the actual validation.
Select a cell, let's say A1. Goto top menu > data > validation.
Choose list and select the range of list 1.

Now goto the cell where you want to put your second drop down list.
Goto top menu > data > validation.
Choose list and type in this formula: =INDIRECT(A1)

If A1 is empty a question will pop up, answer with yes.

And done you are.

Best regards,
Trowa
I've got that much, but what i want it to do is list all the options. So if A1=Hamburgers B1=Hamburger, B2=Cheeseburger, B3=Turkey burger.
But if A1=Pizza B1, B2, B3, and B4 will change to cheese, pepperoni, meat, vegetarian. then be able to change them back.
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Then use IF statements.

B1: =IF(A1="Hamburgers","Hamburger","Cheese")
B2: =IF(A1="Hamburgers","Cheeseburger","Pepperoni")
B3: =IF(A1="Hamburgers","Turkey burger","Meat")
B4: =IF(A1="Hamburgers","","Vegetarian")

Best regards,
Trowa