How to Create Multiple Dependent Drop-Down Lists in Excel | Automatically Update with New Values

List Changes As Previous List | How To Make Dependent Drop Down List In Excel | Indirect Function

In this Excel video tutorial, we will learn how to make a dependent drop-down list in Excel using the indirect function to help us

the list changes according to the choice of the previous list, that is, we will have a main list and a secondary list from the choice of the option of the first list, the second list changes automatically, that is, we can create several lists depending on the choice of the previous list depending on the chosen option previously

to make a dropdown list in Excel make a list of options a checkbox a list of values in Excel we can simply select the cells in the Excel worksheet where we want to apply this list of options and click on data and then on data validation and that's it just select the list option and write all the options you want to have in your list and just separate each option with a semicolon

for example we have categories and subcategories under categories we have options for income and expenses. These are the main categories and for each of these options we have several more options to choose from.

In terms of income, we have an overtime bonus salary and others, as for expenses, we have water, light, gas, food, etc.

so if I choose the revenue option in the category, the subcategory list will refer to the chosen revenue option, but if I choose the expense option, the subcategory list, the second list, the secondary list will refer to the expense options

If I choose option A, a list will appear, if I choose option B, another list will appear, and so on. That's what we're going to learn to do in this free Excel tutorial

the formula that we will use in Excel is the indirect formula or we can still call it the indirect function in Excel we can use the indirect function to return all the cells all the information referring to a set of data

a practical example is what we are going to do in this Excel walkthrough for example I have several lists to choose from and each of these lists is in a different table I have a revenue table and another expense table see the name of my tables are respectively income and expense

this way, if I use the indirect function in Excel and use the revenue criterion, the result of the indirect function will be all the values in the table that correspond to the criteria used, that is, it will return to me all the revenue values, such as salary, extra hourly bonuses, etc. and vice versa.

now with the indirect function we can use it within data validation in excel instead of creating a normal list in excel and separating each of the values by semicolons we will use the indirect function instead and our reference will be income or expense to fetch information from our tables

#JopaExcel #Dashboard #Excel

This is the best explanition I have found so far. Thank you.

[Kibbles]

This video was the closest I came to getting it right! I got as far as entering the 1st dependent list..but it would not work for all the other cells! Thank you. It was a great video.

[dawnnaomi]

Brilliant - dependent drop down finally worked for me! Thank you!

[clearimages]

BRILLIANT - Thanks for the video solved a problem for me

[arkadeusz]

That is simpler than the one I was always doing. I usually went for OFFSET and MATCH combo for the second list. I didn't need to create table for each list separately, but this formula is much cleaner. Always good to learn something new :)

[mahamadbakhitHamdok]

Amazing! Thank you. 9:10 can you force the Category column to delete or rest whenever you select Type?

[MerciaS]

Excellent stuff! Thanks.
It is exactly what I need but on Google sheets it doesn't work. Would it be possible to do it on Google sheets?
Thanks!

[zaneverovati]

Thank You very much for what seems to be by far the easiest way to do this, as well as the cleanest one, as everyone else seems to like the idea of having the variables on the same sheet, I like the way you did it.
One followup question though:

Can I input some sort of sum formula which helps me with getting specific sums, that is - how much was side hustle in a specific month?

Thanks a bunch for the video.

[tanukhanna]

Thank you.... Is it possible to implement the same in Google sheets?

[markhak]

Thank you for your effort that you put in this tutorial. Really straight forward and helpful. 10/10

[rafaelmelendez]

Thanks Jopa! This really helped! Can we use the same process for bring over the table completely with formatted cells (add’l pick lists, etc.)?

[emekaifemenam]

Hi, Good job.
After writing the indirect func and clicking on Alt, it selects only one variable? Please help

[gaspumprepairservice]

Good tutorial! I realize that my comment exceeds the primary scope of this tutorial, but I do have to ask. When changing from “Expense” to “Income”, (at 00:19) the “Category“ field didn’t warn of the mis-match of data. Is there a method to “trap” an error such as this?

[Hibnanos]

What version of Microsoft you're using, as I'm using 2019 and doesn't show the whole data, just extract the data of the first row.

[Ninjhaw]

This is great! However, my table name has space but I think space and special characters are not supported in table names. Do you have suggestions for these?

[budgiebreder]

Can you make it so when you choose for example the rent option the expenses label auto populates? So like you skip entering it but its just there already?

[imamatdalimunthe]

This is cool. I've been toying around with this idea and now you show me how.
Thanks so much.

[rotdec]

Great, It was really simple to extend to all the column. Congrats, very useful

[TheLetsPlayCommunity]

This is awesome, sir! Thank you for the tutorial!

[CLYDETALAMPAS]

Thank you very much sir. Wow, you made it easier! Great job!