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

Рет қаралды 388,517

Күн бұрын

Пікірлер: 133

@1966Kibbles 8 ай бұрын

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.

@usmanabdullahi1893 8 ай бұрын

Have you figured it out yet? I’m having same challenge.

@maheshreddy2174 8 ай бұрын

@jopa Excel im also getting the sane challenge, Can you Please clarify this ?

@ym10up 6 ай бұрын

Check to ensure that your cell reference is not fixed. The cell reference in the INDIRECT function should not have $ anywhere

@dawnnaomi7907 10 күн бұрын

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

@arkadeusz91 7 ай бұрын

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 :)

@arkadeusz91 3 ай бұрын

@@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 :)

@clearimages4120 6 ай бұрын

BRILLIANT - Thanks for the video solved a problem for me

@zaneverovati 9 ай бұрын

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.

@JopaExcel 9 ай бұрын

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 kzbin.info/www/bejne/gYalhYmsZdJmgZo SUMIFS kzbin.info/www/bejne/bZ7Rp2ZrqKqgiKM

@sandeepal1 8 ай бұрын

@@JopaExcel Can we clear the category whenever we are changing Type?

@markhak2911 9 ай бұрын

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

@mahamadbakhitHamdok 10 ай бұрын

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

@rafaelmelendez3956 9 ай бұрын

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.)?

@imamatdalimunthe Жыл бұрын

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

@JopaExcel Жыл бұрын

I'm glad you liked it! Thanks 🙏👍😁

@MerciaS2 8 ай бұрын

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!

@datamech 5 ай бұрын

This is really amazing. Thank you!

@gaspumprepairservice7009 7 ай бұрын

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?

@HarshG28 5 ай бұрын

did you find the way of doing this?

@gaspumprepairservice7009 5 ай бұрын

@@HarshG28 I have not.

@CLYDETALAMPAS 4 ай бұрын

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

@JopaExcel 4 ай бұрын

Thank you! 🙏👍

@rotdec 4 ай бұрын

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

@tanukhanna5350 8 ай бұрын

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

@Ninjhaw 3 ай бұрын

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?

@JopaExcel 3 ай бұрын

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?

@Ninjhaw 3 ай бұрын

@@JopaExcel hmm yeah. I used underscore instead. And perfectly works. Thanks 👍

@budgiebreder 3 ай бұрын

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?

@TheLetsPlayCommunity 4 ай бұрын

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

@emekaifemenam7703 4 ай бұрын

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

@Hibnanos 10 ай бұрын

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.

@samuelkarami4325 8 ай бұрын

This was well explained, many thanks for your time and effort

@ridmiratnayake9573 5 ай бұрын

This was very helpful! Thank you!

@deeward1418 2 ай бұрын

Direct and Helpful. Thank you

@techgalaxy100 6 ай бұрын

Excellent tutorial!! Thanks

@saurabhgaikwad2690 Ай бұрын

It works but for only with specifc block, any other formula for linking with whole column, everyone is open for answers

@SuperKkkkk22 Ай бұрын

Eu sempre procuro conteúdo em inglês porque é muito mais vasto, geralmente no Brasil é tudo muito limitado. Além disso, o alcance é muito maior em inglês.

@juliamayorian1077 4 ай бұрын

Thank you for your effort, it's so helpful

@JopaExcel 4 ай бұрын

I'm glad it was helpful, Julia! 🙏👍

@rehanahmad1306 8 ай бұрын

Thank you. I was doing it the hard way.

@JopaExcel 8 ай бұрын

I'm glad it was useful for you. Thanks for the feedback 🙏👍

@SV-ShyamaArts 3 ай бұрын

Thank you so much God Bless You

@gerekbasikal1 6 ай бұрын

Can google sheet do the same thing like this?

@ajitkumarsingh2654 4 ай бұрын

Excellent boss you solved my problem

@EmmaP2809 5 ай бұрын

Thank you so much! This was super helpful!

@JopaExcel 5 ай бұрын

Glad it was helpful, Emma! 🙏👍

@rohanb6822 7 ай бұрын

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 💯👌🙏

@alpeshshah891 8 ай бұрын

Thank you, very clean and understandable tutorial.

@JopaExcel 8 ай бұрын

Glad it was helpful! 🙏👍

@maheshreddy2174 8 ай бұрын

@@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.

@khmohidurrahman8808 7 ай бұрын

Thanks for such nice explanation . it helped.

@JopaExcel 7 ай бұрын

Gald you liked it 🙏👍

@6coder660 6 ай бұрын

Thanks Man !

@skillpro00000 19 күн бұрын

great man

@hananechamouti7423 2 ай бұрын

Thank you so much ☺

@dyrahdevida856 6 ай бұрын

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 :)

@nicanormedios7467 5 ай бұрын

How did you donit

@shankerramabhotla5376 Ай бұрын

very helpful.

@jeanpavan6816 8 ай бұрын

Super simple and well explained

@Samy-ck8oo 8 ай бұрын

is good if you can reset the second dorop down list in the moment you change the category

@JopaExcel 8 ай бұрын

For sure, I know what you mean. It would be awesome. But, unfortunately we can't do it, maybe via VBA it's possible.

@karthikms1036 6 ай бұрын

Instead of this we can use xlookup function. We can use for big data and also less steps

@graceramil 7 ай бұрын

Thank you!

@ວົງສະກອນທະນົງແກ້ວ 8 ай бұрын

Excellent Thanks

@eglegudaite7798 9 ай бұрын

thank you very much for you video :)

@Malik-ix2kx 5 ай бұрын

Awesome tutorial...very informative....

@purple.fantasy 7 ай бұрын

How do you color code each category?

@rooseveltdeleon5348 9 ай бұрын

Excellent video!!!! Thanks so much,

@JopaExcel 9 ай бұрын

I'm glad you like it! Thanks for the feedback 🙏👍

@SBP-Idea 8 ай бұрын

Thanks Teacher

@ActiveLearningforAll 3 ай бұрын

very helpful. thanks

@JopaExcel 3 ай бұрын

Glad it was helpful!🙏👍

@Anand_03 5 ай бұрын

That Final part INDIRECT(X6) . I have merged cells over there. If i entered X6 shows me Error. How can i Resolve ??

@vinodkumar283 4 ай бұрын

Thanks for the video

@JopaExcel 4 ай бұрын

Your welcome 🙏👍

@stronghomegym 8 ай бұрын

Can you do this in Google Sheets?

@dilankaisurujayalath7040 9 ай бұрын

So for each row in Column E we have to manually enter Data validation from INDIRECT function?

@Leifor1 7 ай бұрын

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

@rotethongvanit 8 ай бұрын

Thanks!

@justwakingup2439 7 ай бұрын

Thanks man! i really needed to learn this 🤍👍😀

@hero9156 5 ай бұрын

Thank you

@April-q4i 2 ай бұрын

What do you do if your headers have multiple words, for example, check fraud? You want a clean drop down that says "check fraud" instead of "check_fraud"

@billal.m 14 күн бұрын

you can anther colonne with a formula =if this cellul equal chek_fraud put chek fraud

@sweetmac17 10 ай бұрын

What happens if I delete the other sheet where I put the options? Is it necessary to include that in my report if ever?

@JopaExcel 10 ай бұрын

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.

@sweetmac17 10 ай бұрын

@@JopaExcel Oh is this okay in any report?

@BangabhabanClub 9 ай бұрын

his really helped!

@JopaExcel 9 ай бұрын

I'm glad it was useful! Thanks for the feedback 🙏👍

@bibhutishaw 2 ай бұрын

10/10

@tanyasrivastava6921 6 ай бұрын

Thanku

@sharathkumar8152 7 ай бұрын

Try selecting the value with / it's not working.can you handle the data which is having many chars such as *,",#,₹

@vilaskumar7008 10 ай бұрын

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

@hewim9443 9 ай бұрын

really cool, and useful

@JopaExcel 9 ай бұрын

Thank you 🙏👍

@tanweerabbasabbas649 Жыл бұрын

nice one😊. its select everything. not every one!!😘

@JopaExcel Жыл бұрын

Ok. Thank you! 👍

@shodhmpl2311 7 ай бұрын

I want add sub item to drop down list item in Google form

@techgalaxy100 6 ай бұрын

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?

@zarinahaciyeva1784 6 ай бұрын

try ctrl+shift+enter after the result with some cells below selected

@techgalaxy100 6 ай бұрын

@zarinahaciyeva1784 i tried but its not showing the way it's showed in the video.

@n12veb9 9 ай бұрын

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

@dilankaisurujayalath7040 9 ай бұрын

Mine too.. were you able to solve that issue?

@aqsanaeem6955 6 ай бұрын

Facing the same prob. Did you find a solution?

@zarinahaciyeva1784 6 ай бұрын

@@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.