How to Create Multiple Dependent Drop-Down Lists in Excel | Automatically Update with New Values
HTML-код
- Опубликовано: 25 авг 2023
- 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 awesome, sir! Thank you for the tutorial!
Thank you very much sir. Wow, you made it easier! Great job!
Thank you! 🙏👍
Thank you for your effort that you put in this tutorial. Really straight forward and helpful. 10/10
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 :)
This is cool. I've been toying around with this idea and now you show me how.
Thanks so much.
I'm glad you liked it! Thanks 🙏👍😁
This was well explained, many thanks for your time and effort
This was very helpful! Thank you!
Excellent tutorial!! Thanks
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.)?
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.
Have you figured it out yet? I’m having same challenge.
@jopa Excel im also getting the sane challenge, Can you Please clarify this ?
Check to ensure that your cell reference is not fixed. The cell reference in the INDIRECT function should not have $ anywhere
So helpful! Thanks
BRILLIANT - Thanks for the video solved a problem for me
This is really amazing. Thank you!
Amazing! Thank you. 9:10 can you force the Category column to delete or rest whenever you select Type?
Thank you so much! This was super helpful!
Glad it was helpful, Emma! 🙏👍
Awesome tutorial...very informative....
Super simple and well explained
Excellent video!!!! Thanks so much,
I'm glad you like it! Thanks for the feedback 🙏👍
Thanks Man !
Thank man that's wonderful
Thank you!
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!
Thank you, very clean and understandable tutorial.
Glad it was helpful! 🙏👍
@@JopaExcel 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.
Excellent Thanks
thank you very much for you video :)
Thanks!
Thanks Teacher
Thank you. I was doing it the hard way.
I'm glad it was useful for you. Thanks for the feedback 🙏👍
Thanks for the video
Your welcome 🙏👍
Thanks for such nice explanation . it helped.
Gald you liked it 🙏👍
Thank you
Thank you.... Is it possible to implement the same in Google sheets?
really cool, and useful
Thank you 🙏👍
his really helped!
I'm glad it was useful! Thanks for the feedback 🙏👍
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.
I'm glad it was useful for you. 🙏👍
About the sum formula with a criteria, you can use SUMIF for one criterion or SUMIFS for more than one criterion.
I have two practical videos that can help you:
SUMIF ruclips.net/video/KUBTVv0m3K8/видео.html
SUMIFS ruclips.net/video/7mnv35sEmRA/видео.html
@@JopaExcel Can we clear the category whenever we are changing Type?
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.
Thanku
Great video ❤ New subbie here 👋
Thank you! 😁🙏👍
Can google sheet do the same thing like this?
Hi How can I make a data validation list from cells in a row. I have a data sheet containing customer name, contact Name 1, Contact Name 2 and Contact Name 3. in another sheet I have a cell where I can (using data validation) pull in customer name. now there is another cell with Contact Name. Now I want the 3 names (Contact Name1...3) show up as drop down. how can this be done? Thank you
is good if you can reset the second dorop down list in the moment you change the category
For sure, I know what you mean. It would be awesome. But, unfortunately we can't do it, maybe via VBA it's possible.
nice one😊. its select everything. not every one!!😘
Ok. Thank you! 👍
How do you color code each category?
Please give me total of better than more excel sheet tricks and ms word symbols create in far than better beautiful❤ lovely symbols in 45 typeses ok so all the best sir i give you some more like this chennel on now 💯👌🙏
So for each row in Column E we have to manually enter Data validation from INDIRECT function?
No, you can use the "small green square" in the bottom right corner to copy the information in the cell.
It should also update from C3 to C4, C5, etc
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?
did you find the way of doing this?
@@HarshG28 I have not.
Can you do this in Google Sheets?
What happens if I delete the other sheet where I put the options? Is it necessary to include that in my report if ever?
If you delete the spreadsheet which contains the options used in the list, the list will no longer works. However, if you don't want to use multiple sheets to place your lists, you can insert the options into the list itself manually.
So, you go to data, list, and source will be like: Option 1,Option 2,Option 3,Option 4...
It is very important not to forget to use the comma to separate each option.
@@JopaExcel Oh is this okay in any report?
That Final part INDIRECT(X6) . I have merged cells over there. If i entered X6 shows me Error. How can i Resolve ??
Instead of this we can use xlookup function. We can use for big data and also less steps
For the indirect function part, mine will show whatever inside the row only. Example, if i create an indirect function expense for row 3, then only the rent will show
Mine too.. were you able to solve that issue?
Facing the same prob. Did you find a solution?
@@aqsanaeem6955
type your indirect function and press enter, you will see just one record as u mentioned above. Later, select this result with some cells below at the same time and press crtl+shift+enter. Unfortunately, u see the records at the number of below cells u selected.
Try selecting the value with / it's not working.can you handle the data which is having many chars such as *,",#,₹
👍
🙏👍
I want add sub item to drop down list item in Google form
I entered the =Indirect(G2) and the only word that appears is the first word in list "rent". May I ask what went wrong in my entry?
Update: I didn't stop until I got the right data. Thank you for your help :)
How did you donit
It doesn't work if Table name having space?
When you rename a Table through "Table design" then "Table name" you can't actually use space. But anyway, it will not work with space.
Maybe you can try underscore or hyphen... 🙏👍
sir plz get it fast do not people time. it was good but always make short video
When you are pressing "enter" it shows you all the values in the table but when I press enter at my pc, it only shows the first value. What am I doing wrong?
try ctrl+shift+enter after the result with some cells below selected
@zarinahaciyeva1784 i tried but its not showing the way it's showed in the video.
It doesn't work on Google sheets... Any similar tips for google sheets?
Have u gotten answer to this. I would like to know thanks
Waow I just tried it and it is working. Click on data, left click on data validation then add rules😅😅😅
Right click...
Hope its helpful
It's seems better on Google sheets than excel
This leads to an error, try enters a different value
Hello, Roland! What type of error? Could you give me more details, so I can help you. Thanks!
@@JopaExcelHi I was editing a sheet on web. Got an error when I tried to name the table after header saying names already exists it should unique.
doesnt work
Hello! Which part of the video did you have a problem with? Give me more details, otherwise I cant help you... Thanks!
Thanks man! i really needed to learn this 🤍👍😀
You might want to show us how to create the dependencies first before you start talking about the other stuff.