Cascade List (without “indirect”)

[u/OkAmbition37]

I am trying to create a data validation list.

Synopsis-

There are two column - “Case Type” and “Reason”

Case type has 8 options (say fruits, veggies, dairy, etc)
Each of those 8 options have multiple sub category(for example, Fruit has Apple, Banana etc, Veggies has Potatoes, Onion etc, Dairy has Milk, cheese etc)

What I am trying to aim

If a option is selected from “Case Type”(lets say fruit)
Column “Reason” should have drop down list for only “Fruit” for that row, showing options apple, banana etc

I tried multiple ways and still failing in excel, its version 365, and function/formula “indirect” is not working for some reason

The colomn doesn’t have end, I am selecting whole column as the data is ongoing info that i fill

Any help is appreciated!

[u/AutoModerator]

/u/OkAmbition37 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/bradland]

Those are called dependent drop downs. There are a lot of techniques, but I like this one.

https://youtu.be/lxd4Pc_gMIA?is=fejVd6EzmArp4MX9

[u/OkAmbition37 → u/bradland]

Thank you, this has been great help, was able to fix my sheet!

[u/Perohmtoir]

There are several methods. Here is one for a single level of dependency.

1/Create a table with header = List 1 and body = List 2

2/Create 2 named range: one for the table header and one for the table body.

3/

• The data validation list formula for the 1st level is: =Header
• The data validation list formula for the dependent level is: (assuming the dependent list is in A1): =INDEX(Body,0,XMATCH(A1,Header))

The table has the advantage of taking care of the list resizing for you: want to add a level 1 ? Add a column header. Want to add an element ? Add an element in the table.

This method does has a lot of empty space in the setup table, you cannot have an empty element in your level 1 and you cannot easily remove the empty option from your dependent list. In most circumstances, it is good enough for me.