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

how to create a dependent drop down list in Excel so here in the type for example I can select either income or expense so I'm going to select your expense for example and as you guys can see here in the category I have a list that has is relying upon the expenses value so we can choose your bet for example but if I change your expenses to income I'm gonna have another type of list and this time this list right here salary investment side hustle and so on so on are all the options that are rely upon the income primary list and of course you can create as many lists as you need to you can create here a hundred different lists to use you just need to follow this step by step so let's go and I really hope this video can help you out and if you have any questions and suggestions let me know comment down below and let's go to the video I'm going to start here creating a new sheet because that way let's just click here in this plus sign that way you can create as many as lists as you need to let me right click in this sheet and then rename uh here I'm gonna give it a name let's say lists and list options for example enter and here let's say in the column B B2 I'm going to create my first list that is going to be income for example ending the column D I'm going to create the second list that is going to be expenses of course you can create as many leases as you need and here I think it's important to separate to split this list is by a blank column because we're gonna transform when the list is done we're gonna take a list and convert it into a table in Excel and that way we need to use here this blank column space to Super the First Column to the first list to the second one for example so income as income I have salary and orders okay and here is expenses are going to have rent and orders okay so I'm done here with these two lists of course you can create another type of leases and uh the thing that I'm going to do here is select the column B and then I'm gonna press and hold the Ctrl key select the column D click here in between one column and another click hold and drag to the right that way we can increase the column size like this for example and this first list right here I'm going to select everyone and I'm going to click insert I'm gonna transform it into a table itself the important question here is my table has headers he has a force because income is a header here in my table so we're going to click here I'm going to select here this box and then click ok I'm going to do the same thing here for the expenses select everyone that make up my expenses list and then insert table my table has headers okay I can also change here the design the layout of this first table for example so I just can click here and then I go to table design and here to the right I can select another type of color uh here maybe I'm gonna choose this green one because usually we use green for income and for expenses I'm going to try to use a red or a orange one like this one for example okay if both lists is done I can come back here to the first pressure and here we're gonna create the first list here in the type and in the second column here the category we're gonna use the secondary releases or the lists that are gonna rely upon this first list that we're gonna have here as this type I can have let's say income and also expensive but instead of manually typing in these two values that I'm gonna have I can select everyone and I can create a list for these two here so income and expense let me click here in the data here to the right I can click in data tools data validation and instead of allow any value I can change in the allow list for example a source I can use expensive or I can also start with income comma expenses for example and then I can press enter and as you guys can see our list is already done so I have no income expenses expenses income I can just click in the cell clicking this in this square right here and select an option in the list and depended on the option that I just selected here my list is going to be automatically changed for me so it can be the first list income or the second Visa that is going to be expenses but you do this secondary list that is going to read like that is gonna depend it upon the first one we're gonna need to use a function that is called it indirect and to use this function let's see here with this radical example so in this Blank cell right here I'm going to type it in equal sign and then in direct this function right here double click it Ubuntu and the first criteria that add this function indirect is asking me is the half text as this first criteria I'm going to type it in the name of any list that I have here so it can be income or it can also be expense let's type it in here income or maybe let's do something better here in this cell right here above I'm gonna type it in income or I can also type it in here expenses okay but uh let's say now in this cell below here the cosine let's make it Dynamic so whenever I change it here the name my indirect acting function is gonna automate it for me the cosine indirect double click here my reference text now is going to be this cell right here where I have the income word flows prevents and then press enter I got here an arrow of course I just type it in here the income that corresponds to the first table but we we know it but Excel doesn't because we need to tell Excel my first list is going to be called it income and the second one is going to be called it expense I need to tell this to excel let's so select the first list right here and then click in table design I'm gonna need to rename this table and here when I have table one as the table name I'm gonna select delete and type it in income for example enter or just click out now the second list here are going to do the same thing table design selective row and table design and then as table name we're going to type it in expensive okay just click out and we're done I have here this Arrow as a result but uh if I just double click it in this function once you and then press enter again as you guys can see now my list is already working and now we can create the data validation using the indirect function if I change here the income to expensive for example enter as you guys can see it's automatically working and uh it's automatic computed for us one interesting fact that is good to tell is if you want to add a new item or if you want to delete something or update something everything that you did you gonna do here in this list is going to be automatically change free so let's say I'm gonna use here income okay I have four different types of incomes but I'm gonna add another one let's say ABC one two three and I you always gonna do this same step you're gonna go to the the last row that you have the last blank Row the first blank row and then you're gonna type it in what you need and then you're gonna press enter as you guys can see the table now I already opened it for me and here to the right as you guys can see the function automatically update and input insert append this new item to the list and uh to delete you just can click here in the list in the the option in the the item that you want to delete right click delete and then delete rows and of course the function is automatically completed free now let's just double click here in the function again I'm gonna code everything here within the cell Ctrl C to cope or right click OK and then I can delete this entire column G now we can come back here where I'm gonna use the list and of course now we can create the data validation I can select everyone here all the cells that make up my category cells or all the cells here that is going to rely upon this first group here and select everyone and then I can go to data here to the right I can select data tools I can select data validation and instead of using allow any value I'm going to allow list for example and I Source I'm gonna control V I'm gonna paste the indirect function but here I need to make some change instead of using cell with G2 as the reference I need to change this reference to using this first cell right here or let's check here cell C3 button c row number 330 row okay C3 so we're going to change here instead of using G2 I'm going to use C3 and then I'm going to press enter and it's already working let me delete here everything and as you guys can see in the secondary release right here if I click in this Square nothing happens but if I change it here the type or income for example as you guys can see in the category now I have the lists that are rely upon the income the income value so salary investment side hustle and orders for example and if I change here the income to expense for example as you guys can see the list now is is using the another list so I have hand at health insurance here and so on and so on so we're basically done I hope you guys enjoyed this video and this is how we can do a dependent list upon another one and of course you can create as many as leases as you want you can create a large list or you can create 3 4 10 100 different lists here in Excel you just need to follow this step by step okay I hope you guys enjoyed this video and if you have any questions or any suggestions to the next videos comment down below let me know and I see you tomorrow as everybody has a new video I see that

Channel: Jopa Excel

Views: 325,145

Keywords: how to, how to excel, jopa excel, step by step, spreadsheet, function, formula, excel formula, ms excel, microsoft excel, excel function, option list, drop down list, excel list, list options, box options, excel dependent list, data validation

Id: LY6xBaLkJVY

Length: 9min 48sec (588 seconds)

Published: Sat Aug 26 2023