How to Create a Dependent Drop Down Lists in Excel (with OFFSET, CHOOSE, INDEX & INDIRECT)

Learn how to create conditional drop-down Excel Validation lists using the INDEX function, OFFSET, CHOOSE and INDIRECT Functions. One aim, many ways to get there. Which one do you like?

🌟 Key Highlights:
- Index Function: Discover alternate uses of the Index function beyond its typical pairing with Match, including creating conditional lists and drop-downs.
- Offset Function: Learn how the Offset function can be a bit tricky due to its numerous arguments but very useful in creating dynamic lists and conditional drop-downs.
- Choose Function: Understand how the Choose function, akin to a condensed version of an "if" statement, can be effectively used in specific list scenarios.
- Indirect Function: Explore the Indirect function's unique approach to creating dynamic drop-down lists, especially useful when drop-down names match range names.

🚀 Practical Applications:
- Create lists that update based on another selection.
- Construct drop-down menus that are conditionally formatted.
- Learn to adjust for volatile functions like Offset and Indirect in large spreadsheets.

Lecture summary:

In this video, I'll show you how you can use the OFFSET formula, CHOOSE formula and INDIRECT function to create drop-down lists that are dependent on another drop down list.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[kuuuyajim]

For INDIRECT function, you can still use headers with spaces because when you use Name Manager, it automatically replace the space " " with an underscore "_" e.g. "List_1". Now, for the dependent dropdown to work, use SUBSTITUTE function to replace the " " with an "underscore "_". Your formula would then look something like this =INDIRECT(SUBSTITUTE (G60, " ", ""_")). I accidently figured this out while I was doing a report.

Thank you Liela for this. I keep coming back to watch your tutorials when I forget them. ❤️

[excelisfun]

Awesome video! Love to see the many methods to accomplish the goal!

[adanrh]

your explanations are clear and to the point, simple but meaningful examples.. THANK YOU!

[rotrose]

Thank you very much for this, first time I learnt to fill the list with formula. The idea is very enlightening. Please continue to share your expertise with us.

[jasvinder]

Very intuitive video, easy to understand for everyone. Thank you for explaining in detail

[bashemf]

Thank you, I learnt many techniques, to add one more concept you can add SUBSTITUTE formula in the INDIRECT formula to recognize space in name manager underscores.

[rajeshdebnath]

Thanks for immediate solution, its really very helpful. Thanks once again.

[jglassner]

Great videos! Glad I found them. There are several videos addressing what I need but I am not sure which one to watch. I have lists that have duplicate data in each column. What I need is a cascading dropdown where I choose Department then the next is Location. Those need to be indexed I think to remove duplicate values. From there I have four cells that should populate with their respective data and are not dropdowns.

[M.A.Sammani]

9:50 Wow, I love it. Thank you so much.

[jaredfromtexas]

Good video. It's always helpful to know multiple ways of achieving something in Excel. What do you mean when you say something in Excel is "volatile"?

[secunatutoriais]

You are great! Thanks from Guinea-Bissau.

[SanjayKumar-yxgc]

Many 2 thanks Leila, you taught us there are four ways to create dynamic list in excel.

[abdulbaseerkhan]

Many thanks Leila! Your every video has something new to be learnt. Keep it up

[jimayas]

Leila, very professional approach. Hands down the best channel for excel tutorials.
In all aspects....

[sofiafredriksson]

This might be the best maker of Excel information videos on Youtube. A big thank you!

[kaushikjo]

hi Maam, this is awesome. I get a feeling your content is directly linked to work situations... Thanks a lot! Your style of coaching is so calm. The speed is just right...

[gopalakrishnarao]

Thanks a lot Madam. Very interesting, informative, useful and practical. Awaiting for your some more videos. A great fan of yours indeed.

[darrengodkin]

In the earlier days of learning to use Excel, I myself started using the Data Validation List option, quite a lot as my first excel worksheet trick.
I would have never thought of using the Dynamic options displayed in this great video using Offset and Choose.
Really impressed!   Another string to my Excel Bow:-)

[Jerrel.A]

Wonderful! Very nice voice combined with clear step by step approach.

[mohamedchakroun]

Thank you leila I love the Offset function