How to Create Multiple Dependent Drop-Down Lists in Excel | Automatically Update with New Values
Вставка
- Опубліковано 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
Thank you for your effort that you put in this tutorial. Really straight forward and helpful. 10/10
This was well explained, many thanks for your time and effort
This was very helpful! Thank you!
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 🙏👍😁
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.)?
So helpful! Thanks
Excellent tutorial!! Thanks
BRILLIANT - Thanks for the video solved a problem for me
This is awesome, sir! Thank you for the tutorial!
Great, It was really simple to extend to all the column. Congrats, very useful
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?
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
Super simple and well explained
Thank man that's wonderful
Awesome tutorial...very informative....
Excellent video!!!! Thanks so much,
I'm glad you like it! Thanks for the feedback 🙏👍
Thank you!
Thanks Man !
Excellent Thanks
thank you very much for you video :)
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 :)
Hi can you help me about that?
@@forhadleobd Sure. You just put all the values a little bit like in the video, so that you have headers in the first row of a sheet (in this example "Income" and "Expense") and corresponding values below it (So column A1 would be "Income", A2 - "Salary" etc. B1 - "Expense", B2 - "Rent" etc.). Then let's say you put your selector (as in "Income" or "Expense" that would determine which list to choose from) in G2 as it is in the video. Then the formula is =OFFSET($A:$A;0;MATCH(G2;$1:$1;0)).
The formula in the video looks much better and it is easier to see what is going on if you for exaple look at this file after a while and try to remember how it works, but the version I wrote doesn't require you to create a separate table and name it every time, and is faster for very large data sets.
In conclusion, if you want something for your home usage or for managing small amounts of data you should probably use the formula from the video. On the other hand if you are managing big database with loads of options, then probably my formula would perform better.
If you have any questions, I would be glad to answer :)
Thanks!
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!
Excellent boss you solved my problem
Thank you so much! This was super helpful!
Glad it was helpful, Emma! 🙏👍
Thank you very much sir. Wow, you made it easier! Great job!
Thank you! 🙏👍
Thanks Teacher
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.
Thank you. I was doing it the hard way.
I'm glad it was useful for you. Thanks for the feedback 🙏👍
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?
Thank you for your effort, it's so helpful
I'm glad it was helpful, Julia! 🙏👍
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 ua-cam.com/video/KUBTVv0m3K8/v-deo.html
SUMIFS ua-cam.com/video/7mnv35sEmRA/v-deo.html
@@JopaExcel Can we clear the category whenever we are changing Type?
Thanks for the video
Your welcome 🙏👍
very helpful. thanks
Glad it was helpful!🙏👍
Great video ❤ New subbie here 👋
Thank you! 😁🙏👍
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.
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?
Thanku
thank you
Welcome! 🙏👍
Hi, Good job.
After writing the indirect func and clicking on Alt, it selects only one variable? Please help
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
How do you color code each category?
nice one😊. its select everything. not every one!!😘
Ok. 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.
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
Can you do this in Google Sheets?
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?
Hello! Space and special characters cannot actually be used in Excel tables currently. My suggestion is to use underscore ___ instead of space, what do you think?
@@JopaExcel hmm yeah. I used underscore instead. And perfectly works. Thanks 👍
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 💯👌🙏
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.
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 ??
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.
I want add sub item to drop down list item in Google form
Try selecting the value with / it's not working.can you handle the data which is having many chars such as *,",#,₹
❤
Instead of this we can use xlookup function. We can use for big data and also less steps
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... 🙏👍
👍
🙏👍
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
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!
sir plz get it fast do not people time. it was good but always make short video
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.
That’s a different video. I watched it earlier today.