Grab the file I used in the video from here 👉 pages.xelplus.com/dependent-dropdown-tabular-file To find out how to extract unique items for your drop-down from a list that can have multiple instances of the same item, make sure you watch this video: ruclips.net/video/7fYlWeMQ6L8/видео.html
Firstly, thanks a ton for the wonderful educational content that you are providing. This doesn't seem to be working with the latest Excel version, kindly advice if there is a different process to achieve this. Thank you Leila
Awesome, I was looking for this functionality in my excel sheets for many days. Dear you are amazing as your tutorials are easy to understand and very helpful. Please give steps to your tutorials so that anyone who wants to learn step by step excel find it more easy.
Hi Leila. I love your videos. The viewers of this should be very grateful of videos like this. I still remember the night when I first used offset match countif within a validation list. Perhaps you have already covered this on another video but if you use offset as an array and add sum infront of it you get a dynamic sum, it's particularly useful for making a YTD model.
Thanks Andrew for your support & your comment. Very happy to hear you find the content useful. I have a similar video here: ruclips.net/video/fkv3o4x_KHUo/видео.htmln - I think you found it already :)
Dear mam, Hope you're good, Because you share this good things to all who all need like me.. Myself I didn't know about how work on excel I'm zero on it. Alhamdhulillah by the grace of Allah... You had came through RUclips I have no words to describe my feelings and share it.... Because my work description mostly depends on Excel spreadsheet and makes reports for week and month so they want all progress.... I think I saw your all videos your way of teaching is fabulous and your English also very clear to understand.. For your video's only on RUclips without skip ads.. I'm not praise you... Thanks for everything Keep sharing May Allah give you reward for your wonderful hospitality to us Thanks again...
I cover dynamic graphs in detail in my online visualization course - on RUclips I have a few as well. I'll make sure to upload more... Excel charts are one of my favorite topics.
Madam, Thank you! Thank you! Thank you so much. I tried sreching this whole night from last 3days.. finally I got it from you.. Thanks. Keeping doing good job & God bless you!!
Great clear tutorial for what is a great spreadsheet design element. Might want to include a brief explanation on how to get around the Data Validation formula bar not recognizing Table and column names as in the comments below from German Vargas, Carla V and Rene Oldenhoff.
Hi Leila, thank you so much for your free content. Is really helpful! Wondering if you can point me in the right direction. I'm using google sheets, trying to replicate this excel tutorial. @ approximately 6mins into this video, you use data validation to make the drop down list. In google sheets I can't seem to get the same results with Data validation when I enter the offset function. Instead the output is the formula, rather than the desired drop down list. Is there any content you have that does this in google sheets? Repeating message from pinned comment of Leila's in hope someone sees this
Just what I needed to make work. Newer version of Excel doesn't return the #VALUE for mulitple cells and does a fill down like the FILTER function. This is great.
Thank you so much for this video it helps me a lot to creat my inventory system. I have one more question how can we make this formula work in more than one sheet.
Hi @leilagharani . theres any video of dependent lists based on id columns instead of header columns? Meaning I got A columns with the id and b column (categoryid and category name) with the display text, and another table with the id and let's say productid and product name. that could be helpful. Thanks in advanced
hi leila, really am thankful that I found your videos, i love your videos! this is a great help for me. You are an expert! I have a question, I am trying to create a CRM in excel sheet (old school), in relation to this video, what if the divisions are not grouped? as I am thinking of adding new contacts at the bottom of my contact list every now and then. I followed your steps in this video, but when it's not grouped, the dependent dropdown list is not the supposed list. Really appreciate your help.
Hi Leila, Just found this video which is great, thankyou. it does exactly what I want it to, when I click in the formula bar and then press F9, it shows the correct data that I want in the 2nd drop down list, but when I try to copy & paste the formula into the Data Validation Source box, excel says there is an error with the formula? I am using data from a Table in a different sheet, could this be the problem? Thanks in advance. Roger
Madam, you are great and expert in Excel. I appreciate/admire your skills/talent in teaching Excel. Once again, sincere thanks from the bottom of my heart, with a fervent appeal to post more number of Excel tricks/tips/time saving techniques. Waiting everyday for your videos.
Perfect solution thank-you. I have another scenario very similar to this. Two columns of source data (on a different worksheet): Products and Processes. The aim is to have data validation for 100 rows of Column A for Products (in a dashboard worksheet) and Process data validation based on the Product, in Column B. Let's say that there are 3 products and 5 processes per product (all different). How might I approach that successfully?
Hi Leila! Thank you so much for your wonderful videos. I'm trying to do this for a table with three nested columns (States, Counties, Schools), but I want to reproduce it for a Table that comes from a Data Query (since I might have to change the country from time to time). I do the same as you did, and when I try the formula outside of the Data Validation I get indeed the array of values (by pressing F9), but when I copy that formula into the list source within the Data Validation, I get an error (it doesn't recognize this as a formula). My guess is that the program fails to recognize the mention to the Table and takes the name of the table and column name as text, rather than a place to look into, but I don't know a work-around. Any idea of how to do this? Thanks!
I was having the same issue and found the solution. Once I copied the formula into the data validation screen I just went in and manually entered the the tab name and the cell ranges for the table column that I was referencing. Works like a charm after that.
Thanks Leila. I see this is an aged video so not sure if I can comment to get help. I have a similar list but my requirement is, I need to be able to see the entire list. Ex.: If I select " Productivity", I want to see all Apps mapped under it. So and so forth for Game and Utility. What's the way?
I loved your video and explaination. Using to enter a sheet of detail expenses, with 2 columns (first looks up Named list (Category), second does lookup based on Category a subset of the Subcategory list (this videos example). Issue I'm having is that when I data sort lines by Category and Sub-Category (so I can create Subtotals by Category) the Data Validation on Sub-Category is all messed up. So line 12 Data Validate maybe based on Line 32 where it came from. Any suggestions?
Could you please make a video showing, choosing from drop down list, Country then State, then District, then city. Selecting country should list only states (provinces) in that country only, like that, selecting province should show districts in that province only. Thanks very much for your videos.
Hi, love these tutorials, as it gives a good overview of functions I don;t normally use. I have to question though, when would you use this drop down? I'm trying to think of where it would be functional but cannot think of anything? Could this be used as a replacement for slicer which are quite limited in function?
Hi Erik - yes - just like slicers, it's another way to add interactivity to reports - so in this case instead of having slicers for all the apps, you would select from a list - and to make sure your list is not so long, you can restrict it by selection division first.
i promis i just today have asked Mr. Mike to give me way to understand offset function because it's really my nightmare but i'm thanks full to you i think i understand it very well in this video thank you so much really appreciate love you
Hey. Thanks for all the great videos. I`m learning a lot, and seeing new ways to use familiar formulas. I would love it if you could maybe start using the FORMULATEXT function while you`re doing these as well. Right now I have to wait for you to highlight a cell again to see the whole formula :)
Hi Leila, please could tell you me- 06:17 when you change the searches on the first dropdown column, the filtered results from the previous search do not disappear on the next column. How can you make them go blank as soon as you select the next search on the dropdown on the first column? Please reply. Thanks.
Hey Leila, that's incredible!! You explain in such an easy way!! I was looking for this and i finally found it. However, this is not my final solution. I need to find a result using 2 conditions. F.E: there is a dropdown list with the value breakfast, lunch and dinner and another one with the values first, seconds and deserts. In the third dropdown i would need de values that match with this two condition. I've been trying to do it using the forumulas in the video but it doesn't work. Can you help me or, at least, give some clues? That'd be great and so useful for me!!Thank you
How do I do this with three or four columns rather than two? It would have a filtering effect so each column completed would reduce the option for the next column(s).
Leila, THANKS. Amazing! But How to solve when we have 3 columns and in the second one we can have a Dependent Drop Down List with the same values (tat will be detailed in the 3rd) and we want to remove the repeated ones? Thanks.
Hi Leila! I love your videos and I have been learning a lot from them. I have a question. I have been trying to use the UNIQUE($A$4:$A$43,FALSE) formula to feed the Data Validation list but with no luck. I even created a Name with the formula but the Data Validation keeps giving the error "The Source currently evaluates to an error. Do you want to continue?" What puzzles me is that I can use an INDEX() formula to feed the Data Validation list, but I cannot use UNIQUE() to accomplish the same. Is there a way to use the UNIQUE() formula to feed the Data Validation list? I appreciate your help.
Dynamic array functions can't (yet) be used directly or indirectly in the name manager - you have to put them in a cell and reference the cell with the hash sign. Check out this video for more info: ruclips.net/video/waqzwMCYD9I/видео.html
I also need to know this, bar just deleting the value, the issue could be that people assume it’s picked up that value automatically if they aren’t used to using my form.
What happen in case is whe create three level dropdown menu. Everything look ok but.... if same values in second column appears in two different values from first column?
Thank you for sharing such informative video, i am seeking some effective method to represent large number of rows and columns (large data) in a single chart,can u suggest any idea.
Thanks leila very good but what will be the solution if data in divison is not sorted or grouped? is there any formulas solution or we have to do this with vba? This will be very very fun
if it's not grouped, I would use a data preparation table in between. So when the user selects the first drop-down, I would first bring out all the occurrences of the app for the parent in a separate table, with the help of Index & Aggregate and refer the second drop-down to this data prep table results. I can add this to my video list if you'd like.
Hey Leila. I'm working with a data set that matches cars with their appropriate bulb types. The cars are sorted by year, make, model and qualifier. The drop-down is set up in the qualifier field, but it returns results for every occurrence of the model name throughout the list, and not just the specific year that the user selected. Is there a way to modify this formula so that it only returns values for the specific year the user selects? Anxiously awaiting your reply. Cheers, Alex
Hi, thanks for your tutorial. I am trying to use this formula to extra names of people who belong to a team. I have 5 different teams and about 160 people divided in them randomly. What I want is to have a my cells displaying team members depending on the drop down menu containing team names. I used organized teams by organizing them alphabetically. In your example games get into a drop down menu. How can I get them listed in each cell one by one? Thanks
Very good stuff! I'm stuck in the last part where we're inputting the function in the data validation. It keeps telling me that there's a problem wiht the formula, even though I get the list I want when pressing F9 (prior to data validation). Any thoughts?
The spill error means the result of the formula is spilling beyond the original cell into other cells. Some of the cells are already occupied by something else. You have to leave these cells empty so the formula can use it to give the results.
I have been trying to use this to create dependant drop downs for a table of data that I have, the formula works and auto spills when I type it in the cell, but when I past to data validation it says there is an error with the formula, any ideas on what the problem might be?
Very good educative specially the offset and choose function. How can we show the unique List of values which is dependent on first Value? Please guide.
You mean the unique list for the division here? I would probably extract the unique list to a separate preparation table using the aggregate function and then with the help of offset get it's dynamic height. I'll add this to my list of videos....
I just want to ask something maam. the same concept but i need for date validation. Like. if i choose January -- same result accordingly for succeeding months options showing Date List (Date Format) January 1, 2017 January 2, 2017 January 3, 2017 and so on and if i choose weekly - showing List 1 , 2 3 if i choose Year showing list January, February, March up to December... thank you maam...
yes.. maam if thats possible because the scenario.. i have the MOOE report that showing the daily outputs of each machine.. yet .. i supposed to have an option that if i choose weekly, monthly or yearly the dropdown list will update according the date category which ive choosen...
Great tutorial, but, how do we make sure that in the dropdown we only have unique ID's? offset is returning all of the values that are the same as the "lookup value", is there a formula to use if you have a huge data set with the same (e.g) "Company and departments" but with other different variables in column c-d-e-etc?
Hi Gilles - I think this is the answer you're looking for: ruclips.net/video/7fYlWeMQ6L8/видео.html - this way you make sure you extract unique items from a list that could potentially carry multiple instances of the same item.
What if the Division are not entered serially? I mean if the list starts with productivity and then right after productivity game division entered and then productivity again and so on.
Thanks for this informative video, i have a question when we change division (Productivity to Game) in drop down list, the dependent drop down list of App remain unchanged and showing "Wencal" in App which is a wrong selection. can we highlight this situation by conditional formatting.
Dear Leila, Is that possible with the tutorial, my Data will be "Book1" file, while my drop-drop list will be at "Book2" file(meaning different file). When i type the formula Data Validation, it shows " You may not use references to other worksbook for data validation criteria".
Hi Leila , I am working on something similar but my data and dropdown is in two different sheets of the same file . I am getting “source currently evaluvates to a error” msg.
Hey, how would you go about if new divisions were added and taken out? For example, it could be a top ten list that changes every week (different divisions coming in and out). Can you automate the dropdown list to adjust to those changes?
You could probably use a data preparation table in between. Depending on the logic of which are the top 10 you want to show... the data validation would then refer to the data prep. table instead directly to the raw data table.
Great Video! Now I have to figure out how to expand the same principle for more consecutive columns. My Programm has 4 consecutive dependent drop down lists referring to one big exercise database (Exercise category -> Subcategory -> Difficulty -> Exercise Name). For each Subcategory there is 4 Levels of difficulty (beginner, intermediate, advanced, expert), which are repeating accordingly throughout the whole range of this column. Any Ideas how to solve that without creating a hundreds of Range Names?
For everybody who has similar problem could check following link. Thank me later :) www.get-digital-help.com/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/#comments
getting frustrated maybe you can help me... I am trying to do, is use a current `(LIST) drop down, and fill in a cell beside it with correct fill in a number format,. so I am trying is the following, have a drop down box to choose an item, and have the cell beside to fill in with number that is associated to the drop down box
Grab the file I used in the video from here 👉 pages.xelplus.com/dependent-dropdown-tabular-file
To find out how to extract unique items for your drop-down from a list that can have multiple instances of the same item, make sure you watch this video: ruclips.net/video/7fYlWeMQ6L8/видео.html
Our teacher (Leila Gharani) is one of the greatest teachers .
Love you .
No other excel website had this formula in it, Leila Gharani is among the best Excel teachers in the world.
Many thanks for the kind feedback Darius!
@@LeilaGharani thats real talk, Leila
Thank you, this is exactly what I was looking for. Took me a bit to get working since i was pulling from different sheets, but works like a charm now.
Exactly what I was looking for. Great video, your teaching is flawless! My job got done.
Cool, thanks!
Firstly, thanks a ton for the wonderful educational content that you are providing.
This doesn't seem to be working with the latest Excel version, kindly advice if there is a different process to achieve this. Thank you Leila
Awesome, I was looking for this functionality in my excel sheets for many days. Dear you are amazing as your tutorials are easy to understand and very helpful. Please give steps to your tutorials so that anyone who wants to learn step by step excel find it more easy.
You save my life for 1000th time, keep it up with the great work! Love your content!
works perfectly well so far. The only thing i wonder is how to implement if division names aren't consecutive?
Then it won't work. I have tried it.
Wow - absolutely fantastic. Fixed a real problem for a team of 50!
Glad to help David.
I am addicted of your all Excel videos... big contribution LG thanks❤️
Thanks Dev for your support. Glad you like them : )
Hi Leila. I love your videos. The viewers of this should be very grateful of videos like this. I still remember the night when I first used offset match countif within a validation list. Perhaps you have already covered this on another video but if you use offset as an array and add sum infront of it you get a dynamic sum, it's particularly useful for making a YTD model.
Thanks Andrew for your support & your comment. Very happy to hear you find the content useful. I have a similar video here: ruclips.net/video/fkv3o4x_KHUo/видео.htmln - I think you found it already :)
Dear mam,
Hope you're good,
Because you share this good things to all who all need like me..
Myself I didn't know about how work on excel I'm zero on it.
Alhamdhulillah by the grace of Allah...
You had came through RUclips
I have no words to describe my feelings and share it....
Because my work description mostly depends on Excel spreadsheet and makes reports for week and month so they want all progress....
I think I saw your all videos your way of teaching is fabulous and your English also very clear to understand..
For your video's only on RUclips without skip ads..
I'm not praise you...
Thanks for everything
Keep sharing
May Allah give you reward for your wonderful hospitality to us
Thanks again...
Many thanks for the kind words. I am happy if the tutorials are helpful to you.
I like it the way you teach very complex formulas in a very basic way. Well done and Thanks!
Very happy to hear that Saman. You're very welcome and thanks for your support.
Do you have any tutorial on making dynamic graphics and lines? which can control the length, height, angle etc.
I cover dynamic graphs in detail in my online visualization course - on RUclips I have a few as well. I'll make sure to upload more... Excel charts are one of my favorite topics.
Thank you... took me a few minutes to get it but you made my day!
You're welcome - it is a bit complicated....I'm glad it clicked :)
Madam,
Thank you! Thank you! Thank you so much.
I tried sreching this whole night from last 3days.. finally I got it from you..
Thanks. Keeping doing good job & God bless you!!
I'm happy that you found it here! Thanks for your kind feedback & your support!
Exactly what I'm searching for. Thank you very mush, you are my Saviour!! Great work there, will share with my friends.
Great clear tutorial for what is a great spreadsheet design element. Might want to include a brief explanation on how to get around the Data Validation formula bar not recognizing Table and column names as in the comments below from German Vargas, Carla V and Rene Oldenhoff.
what would you use in place of COUNTIF if the data is is horizontal and not vertical
Thank you so much for the tutorials. Can I do this automatically in town, city, and country?
Hi Leila, thank you so much for your free content. Is really helpful! Wondering if you can point me in the right direction. I'm using google sheets, trying to replicate this excel tutorial. @ approximately 6mins into this video, you use data validation to make the drop down list. In google sheets I can't seem to get the same results with Data validation when I enter the offset function. Instead the output is the formula, rather than the desired drop down list. Is there any content you have that does this in google sheets? Repeating message from pinned comment of Leila's in hope someone sees this
Got the idea in this video. Very nicely explained, but what to do if I need to have the dependent dropdown list with unique values.
Thanks a lot, it's amazing and so helpful. Brief and concise.
Life-saver to find this again. A whole lot of help it is, thanks.
Thanks, i've been struggled for hours, and finally found this video, thank you so much
Glad I could help
Leila please make video of this for every row. Love you
Just what I needed to make work. Newer version of Excel doesn't return the #VALUE for mulitple cells and does a fill down like the FILTER function. This is great.
Awesome this was a huge help!!!! Thank you! 5:59 for formula
You're welcome!
Thank you so much for this video it helps me a lot to creat my inventory system. I have one more question how can we make this formula work in more than one sheet.
Hi, I would like to make this expandable. Can you please advise me on how to do that?
Thanks for the cool trick!
You're welcome mike!
Thank you so much because of your awesome videos..I have a question,what should we do if divisions were not sorted exactly after each other??
This is too good. Really helpful
Thank you for this, I have learnt something new. You got yourself a like and new subscriber
Hi @leilagharani . theres any video of dependent lists based on id columns instead of header columns? Meaning I got A columns with the id and b column (categoryid and category name) with the display text, and another table with the id and let's say productid and product name. that could be helpful. Thanks in advanced
hi leila, really am thankful that I found your videos, i love your videos! this is a great help for me. You are an expert! I have a question, I am trying to create a CRM in excel sheet (old school), in relation to this video, what if the divisions are not grouped? as I am thinking of adding new contacts at the bottom of my contact list every now and then. I followed your steps in this video, but when it's not grouped, the dependent dropdown list is not the supposed list. Really appreciate your help.
Hi. Thanks. Is there a way to have a blank cell when you change to a new division? I noticed the last selection remains
Thank you for the video tutorial, i tried the approach but when we copy the formula to D6 , E6 shows the values filtered in E5 its not refreshing
Hi Leila, Just found this video which is great, thankyou. it does exactly what I want it to, when I click in the formula bar and then press F9, it shows the correct data that I want in the 2nd drop down list, but when I try to copy & paste the formula into the Data Validation Source box, excel says there is an error with the formula? I am using data from a Table in a different sheet, could this be the problem? Thanks in advance. Roger
Very informative. Good work, helping a lot for the Excel users/learners/addicts/. Waiting for some more. One more big, big salute to your feet.
Thank you very much Gopala for your kind works and your support.
Madam, you are great and expert in Excel. I appreciate/admire your skills/talent in teaching Excel. Once again, sincere thanks from the bottom of my heart, with a fervent appeal to post more number of Excel tricks/tips/time saving techniques. Waiting everyday for your videos.
Perfect solution thank-you. I have another scenario very similar to this.
Two columns of source data (on a different worksheet): Products and Processes.
The aim is to have data validation for 100 rows of Column A for Products (in a dashboard worksheet) and Process data validation based on the Product, in Column B. Let's say that there are 3 products and 5 processes per product (all different).
How might I approach that successfully?
this one is easy to be learnt.
Hi Leila! Thank you so much for your wonderful videos.
I'm trying to do this for a table with three nested columns (States, Counties, Schools), but I want to reproduce it for a Table that comes from a Data Query (since I might have to change the country from time to time). I do the same as you did, and when I try the formula outside of the Data Validation I get indeed the array of values (by pressing F9), but when I copy that formula into the list source within the Data Validation, I get an error (it doesn't recognize this as a formula). My guess is that the program fails to recognize the mention to the Table and takes the name of the table and column name as text, rather than a place to look into, but I don't know a work-around. Any idea of how to do this?
Thanks!
Having the same issue
Also having the same issue!
I was having the same issue and found the solution. Once I copied the formula into the data validation screen I just went in and manually entered the the tab name and the cell ranges for the table column that I was referencing. Works like a charm after that.
Thanks for the lecture, great. What if you have a four column list or more and not the two like in the video?
Thanks Leila. I see this is an aged video so not sure if I can comment to get help. I have a similar list but my requirement is, I need to be able to see the entire list. Ex.: If I select " Productivity", I want to see all Apps mapped under it. So and so forth for Game and Utility. What's the way?
I loved your video and explaination. Using to enter a sheet of detail expenses, with 2 columns (first looks up Named list (Category), second does lookup based on Category a subset of the Subcategory list (this videos example). Issue I'm having is that when I data sort lines by Category and Sub-Category (so I can create Subtotals by Category) the Data Validation on Sub-Category is all messed up. So line 12 Data Validate maybe based on Line 32 where it came from. Any suggestions?
Woohoo!!! Sorted out my problem!!!!!!! Thank you so much!!!
You are very welcome.
Just Discovered your sight on Excel. I usually follow Mike's. very interesting and I will start following yours too from now on :)
Thank you John. Glad to hear that. Mike is great. I love his videos!
Could you please make a video showing, choosing from drop down list, Country then State, then District, then city. Selecting country should list only states (provinces) in that country only, like that, selecting province should show districts in that province only. Thanks very much for your videos.
Hi, love these tutorials, as it gives a good overview of functions I don;t normally use. I have to question though, when would you use this drop down? I'm trying to think of where it would be functional but cannot think of anything? Could this be used as a replacement for slicer which are quite limited in function?
Hi Erik - yes - just like slicers, it's another way to add interactivity to reports - so in this case instead of having slicers for all the apps, you would select from a list - and to make sure your list is not so long, you can restrict it by selection division first.
This is fantastic, great stuff. THANK YOU very much. you made my excel.
Very useful for me,,,, Thank you so muchhhhhh
i promis i just today have asked Mr. Mike to give me way to understand offset function because it's really my nightmare but i'm thanks full to you i think i understand it very well in this video thank you so much really appreciate love you
I'm very happy to have helped Ismail! Many thanks for your comments and your support. Always appreciate them.
Leila Gharani my pleasure
Hi Leila, Thanks for the video. How can i extend the validation to other rows for eg to E6,E7 etc
Did you find the solution?
Hey. Thanks for all the great videos. I`m learning a lot, and seeing new ways to use familiar formulas.
I would love it if you could maybe start using the FORMULATEXT function while you`re doing these as well. Right now I have to wait for you to highlight a cell again to see the whole formula :)
Hi Leila, please could tell you me- 06:17 when you change the searches on the first dropdown column, the filtered results from the previous search do not disappear on the next column. How can you make them go blank as soon as you select the next search on the dropdown on the first column? Please reply. Thanks.
Hey Leila, that's incredible!! You explain in such an easy way!! I was looking for this and i finally found it. However, this is not my final solution. I need to find a result using 2 conditions. F.E: there is a dropdown list with the value breakfast, lunch and dinner and another one with the values first, seconds and deserts. In the third dropdown i would need de values that match with this two condition. I've been trying to do it using the forumulas in the video but it doesn't work. Can you help me or, at least, give some clues? That'd be great and so useful for me!!Thank you
How do I do this with three or four columns rather than two? It would have a filtering effect so each column completed would reduce the option for the next column(s).
I would love to know this too.
Please explain,
If division remain un-order list, then what should we do?
Leila, THANKS. Amazing!
But How to solve when we have 3 columns and in the second one we can have a Dependent Drop Down List with the same values (tat will be detailed in the 3rd) and we want to remove the repeated ones? Thanks.
This is also my problem. Please help!!
Thanks so helpful video 😊
Hello LG,
Beautiful videos all around.
Thank you!
Hi Leila! I love your videos and I have been learning a lot from them. I have a question. I have been trying to use the UNIQUE($A$4:$A$43,FALSE) formula to feed the Data Validation list but with no luck. I even created a Name with the formula but the Data Validation keeps giving the error "The Source currently evaluates to an error. Do you want to continue?"
What puzzles me is that I can use an INDEX() formula to feed the Data Validation list, but I cannot use UNIQUE() to accomplish the same. Is there a way to use the UNIQUE() formula to feed the Data Validation list? I appreciate your help.
Dynamic array functions can't (yet) be used directly or indirectly in the name manager - you have to put them in a cell and reference the cell with the hash sign. Check out this video for more info: ruclips.net/video/waqzwMCYD9I/видео.html
How do we make E5 go blank first whenever D5 changes value? I have been learning a lot through your videos. Kudos!
I also need to know this, bar just deleting the value, the issue could be that people assume it’s picked up that value automatically if they aren’t used to using my form.
Thanks for sharing Leila, I wonder this won't work having data on a separate tab sheet right?
You're welcome. This should also work with data on a separate tab sheet...
What happen in case is whe create three level dropdown menu. Everything look ok but.... if same values in second column appears in two different values from first column?
Thank you for sharing such informative video, i am seeking some effective method to represent large number of rows and columns (large data) in a single chart,can u suggest any idea.
You're welcome Fahad. Too many categories in a chart can be confusing for the reader. Is it possible to group some categories?
Thanks leila very good but what will be the solution if data in divison is not sorted or grouped? is there any formulas solution or we have to do this with vba? This will be very very fun
if it's not grouped, I would use a data preparation table in between. So when the user selects the first drop-down, I would first bring out all the occurrences of the app for the parent in a separate table, with the help of Index & Aggregate and refer the second drop-down to this data prep table results. I can add this to my video list if you'd like.
Yes it will be very kind of you if you add it.
Leila Gharani can i know wich video to handle that problem? Many thanks
The video is not on RUclips yet - I hope to have it up in January.
Leila Gharani thanks leilouna i am looking forward
Hey Leila.
I'm working with a data set that matches cars with their appropriate bulb types. The cars are sorted by year, make, model and qualifier. The drop-down is set up in the qualifier field, but it returns results for every occurrence of the model name throughout the list, and not just the specific year that the user selected. Is there a way to modify this formula so that it only returns values for the specific year the user selects?
Anxiously awaiting your reply.
Cheers,
Alex
Hi, thanks for your tutorial. I am trying to use this formula to extra names of people who belong to a team. I have 5 different teams and about 160 people divided in them randomly. What I want is to have a my cells displaying team members depending on the drop down menu containing team names.
I used organized teams by organizing them alphabetically. In your example games get into a drop down menu. How can I get them listed in each cell one by one?
Thanks
Thanks for the video.. it really helped me .. but doesn't work when I upload this Excel in Google sheets
Very good stuff! I'm stuck in the last part where we're inputting the function in the data validation. It keeps telling me that there's a problem wiht the formula, even though I get the list I want when pressing F9 (prior to data validation). Any thoughts?
You can download the workbook from the link in the description and cross check your formula.
Love your videos. I have learned so much from you. Thank you! I'm getting a #SPILL error. Would love to know what I've done wrong.
The spill error means the result of the formula is spilling beyond the original cell into other cells. Some of the cells are already occupied by something else. You have to leave these cells empty so the formula can use it to give the results.
Excellent ....pls make a video on bank reconciliation with cash book account automatically ... I will be obliged to you.
Good idea. I'm waiting for the work book
Workbook has been added : )
Thank you
Is it possible to make list depend upon the list as it occurs in drop-down list depending upon drop-down list?
I have been trying to use this to create dependant drop downs for a table of data that I have, the formula works and auto spills when I type it in the cell, but when I past to data validation it says there is an error with the formula, any ideas on what the problem might be?
very informative tutorial.... thanks Leila...
You're welcome Ronnie.
Thanks - very helpful
Very helpfull video. Thank you so much.
You're very welcome Gue.
This is working fine when Division is ordered one by one, But "Division" is Unorder or Shuffle, do you have any solution!?
This is awesome, Thank you Leila!
You're very welcome Chaminda.
Very good educative specially the offset and choose function. How can we show the unique List of values which is dependent on first Value? Please guide.
You mean the unique list for the division here? I would probably extract the unique list to a separate preparation table using the aggregate function and then with the help of offset get it's dynamic height. I'll add this to my list of videos....
Thank you very much.
Any ideas on how could this work if our database was in a different sheet?
Awesome, thanks, Leila.
I just want to ask something maam. the same concept but i need for date validation.
Like. if i
choose January -- same result accordingly for succeeding months options
showing Date List (Date Format)
January 1, 2017
January 2, 2017
January 3, 2017
and so on and
if i choose weekly -
showing List
1
, 2
3
if i choose Year
showing list
January,
February,
March up to December...
thank you maam...
Do you mean that the formatting of the 2nd drop-down changes based on what the user selects in the first drop-down?
yes.. maam if thats possible because the scenario.. i have the MOOE report that showing the daily outputs of each machine.. yet .. i supposed to have an option that if i choose weekly, monthly or yearly the dropdown list will update according the date category which ive choosen...
amazing - this has really helped and you made it so easy to follow:)
Great tutorial, but, how do we make sure that in the dropdown we only have unique ID's? offset is returning all of the values that are the same as the "lookup value", is there a formula to use if you have a huge data set with the same (e.g) "Company and departments" but with other different variables in column c-d-e-etc?
Hi Gilles - I think this is the answer you're looking for: ruclips.net/video/7fYlWeMQ6L8/видео.html - this way you make sure you extract unique items from a list that could potentially carry multiple instances of the same item.
Can you do this for more then one column?
What if the Division are not entered serially? I mean if the list starts with productivity and then right after productivity game division entered and then productivity again and so on.
works perfectly when the column A has consecutive values. The only thing I wonder is how to implement if division names aren't consecutive?
Thanks for this informative video, i have a question when we change division (Productivity to Game) in drop down list, the dependent drop down list of App remain unchanged and showing "Wencal" in App which is a wrong selection. can we highlight this situation by conditional formatting.
Yes- it only changes or "refreshes" once we actually click on the list. An option would be to force it to refresh through VBA....
hoping to see this cool trick in coming videos....
Dear Leila,
Is that possible with the tutorial, my Data will be "Book1" file, while my drop-drop list will be at "Book2" file(meaning different file).
When i type the formula Data Validation, it shows " You may not use references to other worksbook for data validation criteria".
Hi Leila ,
I am working on something similar but my data and dropdown is in two different sheets of the same file . I am getting “source currently evaluvates to a error” msg.
Hey, how would you go about if new divisions were added and taken out? For example, it could be a top ten list that changes every week (different divisions coming in and out). Can you automate the dropdown list to adjust to those changes?
You could probably use a data preparation table in between. Depending on the logic of which are the top 10 you want to show... the data validation would then refer to the data prep. table instead directly to the raw data table.
Hi.. thank you for your video.. really helpful, but i have question, what if my database from the division is random, not grouping?
then you need to watch this video: ruclips.net/video/7fYlWeMQ6L8/видео.html
Great Video! Now I have to figure out how to expand the same principle for more consecutive columns. My Programm has 4 consecutive dependent drop down lists referring to one big exercise database (Exercise category -> Subcategory -> Difficulty -> Exercise Name). For each Subcategory there is 4 Levels of difficulty (beginner, intermediate, advanced, expert), which are repeating accordingly throughout the whole range of this column. Any Ideas how to solve that without creating a hundreds of Range Names?
For everybody who has similar problem could check following link. Thank me later :)
www.get-digital-help.com/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/#comments
getting frustrated maybe you can help me...
I am trying to do, is use a current `(LIST) drop down, and fill in a cell beside it with correct fill in a number format,.
so I am trying is the following, have a drop down box to choose an item, and have the cell beside to fill in with number that is associated to the drop down box
What can I do differently if the data in the drop-down list is incorrect? I am stuck.
Hi Leila, what if in the second column has non unique item?