Create a Dynamic Drop Down List in Excel
HTML-код
- Опубликовано: 14 окт 2024
- Do you want to learn how to create a drop down list? That's when you can click a small arrow to show you a list of choices. But what about after selecting an option from a first drop down list and it filters another set of options from another drop down list? That would dynamic wouldn't it? This video covers how to create a dynamic drop down list in Excel and it's pretty simple to do. See how...
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
If this video didn't fit, there are some other Dropdown List video at ruclips.net/p/PL-n8f1cY_Qw9-ZSEpRN3OLmzYp4w54vht
...
Thank you! I love that you speak clearly and do not rush through.
Thanks for your comments!
the video is fine, but you could use a sandwich and a cup of coffee... try being more "dynamic" in your videos because you sound.... sad....
Perfect! A sophisticated use made easily comprehendible. Thanks Doug.
You're welcome!
Watched another tutorial on the same topic. This is is much better... may not be for excel starters... but if you know the basic. this one is what you need for dynamic dropdown list. Thank you Doug!
Hi mbl0729, thanks for the comment!
Thanks for this, trying to learn as much as possible about excel. Drop down lists are something I'm trying to incorporate in a project for my job. This tutorial is just what I needed.
Thanks Rick Galloway, glad it helped!
Thanks, very easy after watching your explanation.
Glad to hear that!
Thank you from Brazil!!!
Thank you Doug. I referred other 2 video to create drop down list for my Excel 2007, but In Spite of following right process, I was not able to create "table name" by selecting reference cells. Your "define name" option helped me to do that....I must say this video is highly technical and it covers many aspect of this topic. Thanks again.
Hi Manoj Upadhyay, thanks for the comment!
It really helped me to create a dynamic drop down. Thanks for sharing.
Hi Prakash Murugeshan, thanks for the comment!
Thanks for the short and lucid tutorial Doug; you just resolved my issue!
Glad it helped!
Thanks Doug. Awesome video.
Hi Blackend65, thanks for the comment!
New learning! Having fun doing it on my own, thanks!
It's nice & simple...thank you for sharing...looking for advance example of dropdown...please share😊😊😊
Hi vivek chourasia, glad you liked it, thanks for commenting!
Thanks Doug for the great tutorial. It was really helpful.
Glad you liked it!
very well explained. Thanks a lot. I tried to understand it with other videos but it wasn´t so clear. It is also nice the way to show the shortcuts. Very helpful. :)
I have a question. Is it possible to send you a private message?
Great video! You made it look simple and to the point.
Thanks for the comment!
good video, tough for a newbie like me to follow. but i still learned a bit. i am going to watch a few times and practice along. Thanks Doug!
Hi Ernesto L, thanks for the comment!
Nice job! Thanks for your expertise and show-how in this tutorial.
You're welcome, thanks for your comment!
Very nicely explained!
Thanks.. really helpful.
You're welcome and thanks for the comment!
awesome Doug it is very much helpful to me...thank you
Glad it helped!
wow great tutorial i looked for this because im starting to work in a auto part and need to rebuild the inventory database thank you very much
You're welcome!
very useful. thanks Doug
Hi Lancelot Green...you're welcome, glad you liked!
Sir, I love you. Thank you for this!
Thanks!!
Extremely informative. Thank you so much.
Hi Mark McKay, glad you liked it, thanks for commenting!
Thank You so much .I learn so my different and new things in excel,as as new learner.
You're welcome!
Superb man....thank you !!!!!
Thanks for the comment!
At last!! i got it...tq for the video... u save my life!! hahaha.. it help me so much.. but how to drag the format for category 2? if i drag down, the drop down list follow the first column of category 1.
+Doug H Dear Doug,
The video is very informative. Many thanks for it. I am also encountering the same problem and it doesn't get solved by format painter too. Please suggest some remedy at the earliest. Thanks in advance.
Great Tutorial!!!
Hi Jason Swami, thanks for the comment!
Thank you Doug!
You're Welcome!
Hey Doug i like so much how you explain this tutorial is there a way you can create a searchable drop down list such that you just type to get the result or suggestions Plleassee Heeelp i kindly ask it's a bit urgent ........
excellent video. thanks for posting.
Thanks for the comment!
Fantastic! Really very usefull! Thanks!
Thanks Paulo Yvens, glad it helped!
Yes, it helped. I'm loving your tutorials. All usefull! Thanks for you job.
Hi I was wondering if it was possible to use a pivot table (stripped of all headers/titles, totals, etc.) to create a list source?Thank You for your help.
Very helpful,appreciate that!
THANKYOU SO VERY MUCH.. IT WAS VERY HELPFULL
Hi manojiittm, glad you liked it, thanks for commenting!
Thanks.. the Video helps a lot..
Glad it helped!
Please make a video regarding "Construction Project Management Dashboard" in Excel.
Thank You ... Appreciate Your Time ..
Hi Rohit Behera, glad you liked it, thanks for commenting!
You give Every Details.. From Shortcuts to Detail... Please keep up the Good Work
It was very helpful to me as I am using it for the first time. Thank you.
You're Welcome!
Very Very helpful amazing helped me alot :-)
Hi Ahmed Ade, thanks for the comment!
awesome tuts
Glad you liked it!
yeah i have seen it and it is such a wonderful piece of work i was wondering whether there is a way i can make the combobox searchable using any character for in a drop down list if i type letter A i get all the words with letter A regardless of the letter A's position even if it's the last character in the word....
Great video, I am a shortcut user as well, and the video content is great and helped me alot. Thank you.
Hi J Do...you're welcome, glad you liked!
Thank you so much you were amazing.
Hi Qumail Haji, thanks for the kind words!
very nice .......ur genious sir
Hi Deva Ugvekar, thanks for the kind words!
Doug, great video! I am trying to sort data using two drop downs and then have a cell present data from another cell based on the criteria of the two drop downs.
As an example:
Say I want to sort fruit. I would have one drop down that has type of fruit and another drop down with fruit distributors names. By selecting a fruit and a distributor I want to be able to filter and see what the price would be. Do you have any suggestion on how i could do this? Thank!
These drop-down are good for dashboards, but if you want to do some analysis like that I'd suggest using the table feature to do your filtering (see ruclips.net/video/Knk_MD_eOpc/видео.html) . or if you want to get fancy after creating your table use a Pivot table to do some analysis (see ruclips.net/video/ZQxgYtl33xY/видео.html)
Hi Doug. Thanks for the tutorial. How to reset category 2 box to blank when category 1 box value is changed. Right now category 2 value doesn't change when a new option is selected in category 1. Thanks for the help in advance.
It should change but may be affect by the way formula calculation is set in your setting...is it manual or automatic? See support.office.com/en-us/article/Change-formula-recalculation-iteration-or-precision-73a772d5-6224-407c-8029-60a47488b6aa
Hello again. Its set to automatic. The link you sent talks about changing the modes to either automatic or manual but still doesn't address the issue. The dependent should go blank when the main option is changed. Appreciate your help.
Excellent. I learnt a lot. thanks for uploading such a nice and informative videoes. Mian Naveed Iqbal Pakistan
Hi Naveed Iqbal...you're welcome, glad you liked!
thank for a very useful video, sir may I use offset formula in 2nd drop down menu to extension or removing from list of (car model )keeping with only 1 cell blank every case
Hi! I'm wondering if you have a tutorial for this using Microsoft Office Professional Plus 2010? This one is AMAZING! But, I seem to get lost (as in - some of the commands/steps don't work) when you get to the sub-categories and THAT is what I need to do for a project I am working on today.
Hi thesongmuse1, thanks for the comment! Hi thesongmuse1, you're welcome!
First time learners, such as my self, find shortcuts skip establishing and reinforcing basic understanding; and they are hard to memorize. It's easier if you avoid shortcuts so one may see which tabs and selections you use. It creates a visual mapping to find the tools and builds on things we are already familiar with.
Thanks for the feedback! I'll keep that in mind for future vids
SPASH Girls Soccer I disagree, shortcut keys are easier especially if you do repetitive tasks you just need a sequence and can work without stopping to find the mouse...plus the tab contents move around with each excel edition, but shortcut keys stay the same..
I find both helps. To show the visual route and a reminder of the shortcut. Both are valuable. :-)
Yes, but everyone, my self included, does not know every shortcut yet. It's easier to to learn the long way first, then learn a more efficient way to complete. Example.... You learn long division before you teach yourself how to efficiently do it in you head(a shortcut, like keystrokes). Keep in mind that there are people that dont have your vast shortcut knowledge.
this teacher was terrible
Hi Doug, I want to use data from another sheet to create these drop down lists. How can I get it to source the data from say, sheet 2?
great video
Thanks for the comment!
The best!
Bundle of thanks
Hi Faisal Ikram, you're welcome!
How do you get the font size of your drop down list larger, comparable to the sheet itself?
This is great, however, I want to, if this example, the Vegetables I want to by a hyperlink to another worksheet so my
3 columns would be Town, list of Postcodes then on selecting a specific postcode, this would hyperlink to a price list. So, I get the theory to the final bit but not sure how I create the hyperlink in this context ???
Hi Joint Account, sorry 🙁....but try a post on the mrexcel.com forum!
I have a drop down for a summer camp for t-shirts....XL, L, M, S. Is there a way to total up how many XL there are in a column?
Doug, what if I was to keep adding information into my lists that I'd want to appear into the dynamic list when updated? Would i have to do this process again?. What I want to do is make a list of dates when I have visits from customers, when I click on that date it will show me the notes from that day. Thanks!
if your source list is using the table feature (ruclips.net/video/Knk_MD_eOpc/видео.html) , then updates to that table will show up downstream in the dynamic list.
this is EXACTLY what I wanted, except I want a price linked to it too!
Glad you liked!
Doug, (1) What is the different between dynamic list and Searchable drop down menu? (2) Which formula would I use, if I have more than just 2 rows to pick from? In your example, you only used the American and Japanese. If some one has like about 5-7 original lists, and it becomes shorter, and shorter from the list, as the person continue to pick their choices . Thank you
the dynamic list filters out your next selections as you make further selections. A searchable dropdown list in on column of records would filter out values in one column versus the dynamic list is working with different source columns of records.
Dear Mr Doug,
Is it possible that I can do a third dropdown list on the next category 3 column using indirect function
Thanks
+Joseph Tabone
Dear Mr Doug,
I managed to solve me problem by not using spaces in titles and names
Thanks
REgards
Hi Joseph Tabone, thanks for the comment!
Thank you.
You're Welcome!
Heey Doug H, I have been trying this myself... Works great when I make it just like you show it. However when im trying this at work on a decent database file ( I need to create the ability to associate company names with employees) for some reason this is not working... Could you help out?
Good video
Thanks!
nice video.
I have a query! If there are some empty cells in American or Japanese in the above examples, then how can we escape from those ie I want only values but not empty cells in the second dependent drop down.
Thanks in advance
***** ok.. thanks for quick reply
Doug, I'd like my drop down list to trigger, not another drop down list but a value, how do I do that.
Is there anyway that you can create a drop down list by selected specific cells e.g. A1, A3, A5 instead of selecting all the cells?
OK, Indirect is a good example for it however, when you need to make a data that will cinsist 2 words such as Green Apples, then The Table Culloms can be renamed as Green_Apples of GreenApple as values in table rename are not allowing to be space separated. If you could give a solution to it? or maybe workround
Unfortunately for names ranges (tables too) don't take spaces. You may want to use SUBSTITUTE function in addition to INDIRECT. See the MSFT article for some insight answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/named-ranges-with-spaces-and-hyphens/18ddf5f9-7751-e011-8dfc-68b599b31bf5
Thanks Doug.. It helped me a lot at the job. Good one.
Thanks Doug!
Q: is it possible to crate say... a list of clothes that will show a product number in a cell?
ex: cell1 you select sweater or pants, next cell you choose size.
now what i am after is that if you select sweater in cell1 this will give you the 1st part of a product nr "1234".
then "1234" will be shown in cell 3.
when you then choose the size from cell2, you will be given the remains of the total product number shown in
cell 4. so you then get a complete product number spitt over two cells.
you get the idea?
much much muuuuch appreaciated, if you could help with this :)
thank you!
Thanks
You're welcome!
Thanks you saved my day :)
You're Welcome!
Is it also possible to create a dropdown list in which it automatically also adapts the background colour of the cell?
probably with conditional formatting. See this video for some insight
ruclips.net/video/meogoA_MXmg/видео.html
thanks sir
Hi Deva Ugvekar, you're welcome!
HI, When building this, do the lists have to be on the same sheet?
Source list can be on separate sheets
thanks !
You're Welcome!
Can you add a dropdown checkbox in excel form? Like "Allergies" for item and then a dropdown list that lets people check all that apply. ie codeine, bee venom, penicillin, peanuts, shellfish
Hi Kevan Stone, thanks for the comment..yes you can
thank you
You're welcome!
Hey Doug,
Thank you for the video.
I am trying but for some reasons I wonder if the message that appears that the source is invalid lead the second list is not clickable. Do you know why this is happening?
your range name can not have any space. You may replace with underscore
Hi Doningnon Soro, thanks for the comment!
The only problem I encountered while I was doing the exact same methods is when I create more than one row, it just sticks with the options of the first row only, the remaining rows won't interact with your selection... any idea how to fix that will be greatly appreciated. Thanks for the great video.
I keep having the same problem, have you found a tip to fix this ?? been at it for hours, I'm giving up and asking for help. thanks :) very much appreciated
Hi Mohamed Sugal, thanks for the comment!
Is there any way to have Excel insert a default value in a Dropdown List?If we take your example with cars: If you select 'American' from the Category1 Dropdown List, Category2 will automatically get either the 'GM' or the 'Ford' value. You should afterwards be able to change the value of Category2 (by Dropdown), if the default value is not correct.
I have done exactly as instructed and have 5 tables to reference but it only took the first table and won't dynamically display the other choices regardless of what I select. What am i doing wrong
Hey just wondering how I can paste the formula down a row. In Row A Selection for multiple drop downs, but I don't want my drop downs in Section B to all be dependent on A1. Does that make sense? I've been trying to figure out the copy and paste and can't seem to
Any help is much appreciated
Hi Deedee B., sorry 🙁....but try a post on the mrexcel.com forum!
Can you create a separate pie chart or statistics related to how many hondas or nissans you sold?
Hi, Doug - thanks for explaining in the most simple way!
I have a doubt here, suppose if we select "vegetable' in category 1 and 'potato' in category 2, then if we modify category 1 to ' fruits', still 'potato' remains in category 2, which is wrong. Is there any way by which category 2 becomes blank if the data in category 1 changes.
if the source table/list is where the change is done and not the cell where the dynamic dropdown is selected/overwritten then it should work. Editing it in the cell where the dropdown is will have this limitation.
Hello the feature you have shown is for two option what if i have to choice three function like for eg: if i have select a city from it it has to select a hotel & when i select the hotel how will it select the country it is in. can you please help on this
Hi fadreen, thanks for the comment
Hey Doug can i use the same method [indirect(Cell)] for a third category? ...I want to do Region, Contractor then the third is ship to location
Yep should work.
can i use formula instead of vegetables or fruit.
for example , suppose there is condition A, B, C, and if A condition come then use different formula and if B condition come then different formula ...and so on.
(formula means may be any condition , add, substrate, divide, multiply etc.
can you help me plz ?
Have you tried to use the data validation with the IF function? See IF function video here ruclips.net/video/vNmZThJltOw/видео.html
Doug H thanks sir
I am able to create the dynamic drop down lists except there is one issue....when I select the cells of data to name the group of cells, it won't let me type out the header with spaces...it makes me take the spaces out. Then I can't put spaces in any of the selections either or my drop down lists won't populate...how do I fix this? If i take out all the spaces between words it works...but not if there are spaces....
can you reference a POSITIONS? I have to go 3 catagories deep and sometimes have to change the first or second category for work.
Sorry, don’t understand the comment/ question...
Hi! I'm trying to figure out how I can select multiple items from my drop down list (instead of just one item) and instead of filing in the cell with the full word, what goes into the cell is just the code. Example... Instead of Phone Conference and Face to Face Meeting, Phone conference would be PC and Face to Face Meeting would be FF.
Not really with this option...maybe with checkboxes (ruclips.net/video/BCss2QMSlM4/видео.html). Another option is to use Slicers ruclips.net/video/4XdzSSoS-bM/видео.html or ruclips.net/video/A0VAAsFL4Hs/видео.html
Unrelated question to the video.
How can you center text between top and bottom cells?
Example. Center text between a1 and b1
Instead of merging cells.
thank you
sir can you tell me how to take selected data from a list to appear in normal form and make it to a list without drop down option
Hi srinivas sidharth, sorry I don't do consulting 🙁....but try a post on the mrexcel.com forum!
How do you get background colors for cell formatting to show up. I created a drop down and the cell's backgrounds remain white when I select a menu item. How do you add background colors to work books that will show up when added to the main page?
Do you have a video of that?
Thank you! :)
Hi Ryan Johnson, thanks for the comment!
Very usefull
Hi Amadou Idiguine Adamou, glad you liked it, thanks for commenting!
How do you create keyboard shortcuts to select items in your drop down list?
Hi F W, sorry I don't do consulting :-( ....but try a post on the mrexcel.com forum!
How to do for three categories If eg: Country (France is selected it will show city according to your video, because two category. what if i need third category where if i first select country france then it will show all the cities like paris, lyon etc then if i select paris it should show places in paris. how to do that??
it would be a the similar steps repeated as far down as needed
in the final step when you used Indirect how to make it depend on two cells ?
example im making Three Cells
1- Company Name > (company 1 , company 2 , company 3)
2- Departments > ( HR, Management,Sales,Marketing)
3-Company 1 > (HR employees) (Employee 1 , employee 2)
Company 1 > (sales employees) (Employee 3 , employee 4)
Company 2 > (HR employees) (Employee 5 , employee 6)
Company 2 > (sales employees) (Employee 7 , employee 8)
tried to use "&" as a join?
thanks
You're welcome!
Its Just for single cell. I want to put dynamic drop down box in whole column. Every time I select the whole column it only shows me for the fruits name not vegetable. I hope you understand my question
You may need to create the name range for the whole column (i.e. B:B)