In this video you’ll learn a lot about combo box drop-down lists in Excel:
1. How to create a combo box drop-down list
2. How to create a dependent combobox drop-down.
3. How to restrict the combo box drop-down to exclude blank cells and the best part;
4. How to overcome the problem we face with dependent data validation lists where the dependent list doesn’t reset once the value for the first list is changed – you’ll learn an alternative method to overcome the failure to auto-reset (we’re going to automatically refresh the dependent list once the value for the first list changes)
⯆ DOWNLOAD the workbook here:
LINKS to related videos:
Dependent drop-down list playlist:
Index Match basics:
★ My Online Excel Courses ►
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
EXCEL RESOURCES I Recommend:
Get Office 365:
Microsoft Surface:
GEAR
Camera:
Screen recorder:
Microphone:
Lights:
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
Nguồn: https://marishka-moi.com/
Xem thêm bài viết: https://marishka-moi.com/category/cong-nghe
great video!! very well articulated. subscribed 🙂
That's is awesome, can you copy the combo box in multiple rows.
Hello there….thank you for your videos…
If anyone can help , Example : we have these 3 cells as above Select Div, SElect App, REvenue i want to find a way where i will select in productivity div (WenCal ) and the the cells with Select App and Revenue will take the values Fihtrr and Commmuta respectively automatically
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
good morning, leila!
as always your videos give a lot of valuable input..
however, i have seen anything which helps me with a certain scenario –
your ideas on how to solve this scenario would be apprectiated:
there 3 colums of raw data: col.a) english, col.b) german, col.
c)company. these are expanding columns and data is entered unsorted in
the columns. there are also many duplicates, which are not needed.
the columns are dependant on each other:
german english company
selbstbewusst – self-assured – comp. d
neugierig – curious – comp a
überzeugend – convincing – comp. f
kraftvoll – energetic – comp. c
kreativ – creative – comp.h
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
german english company
kraftvoll – energetic comp. c
kreativ – creative comp. h
neugierig – curious comp. a
selbstbewusst – self-assured comp. d
überzeugend – convincing comp. f
and
english german company
convincing – überzeugend comp. f
creative – kreativ comp. h
curious – neugierig comp. a
energetic – kraftvoll comp. c
self-assured – selbstbewusst comp. d
as i mentioned, the colums are very long and expand constantly. also there are many duplictes which i do not need.
perhaps i have not explained myself in a understandable way: if it i could send you an excel file.
hopefully you can squeeze my question into your busy schedule.
many many thanks in advance.
colin huntley
Excellent tip, thanks!
Thanks Leila, I prefer the combo box approach. 👍
Thank you- You've made me to fall in love with excel all over again (sounds nerdy!)
Very explanatory,
Thank you so much
This is an awesome video and works pretty well in my application. However, I ran into an issue with the index number and the different number of values. Example, if the previous selection returned row 10 as the linked value but the next selection only goes to 7 values, I get an #N/A error. What I need is for it to reset the index link back to the 1st value.
Hi Leila. I need to make a form that skips the following line (because it is autogenerated with the data from the previous line). Thanx
got it ….thanks!!
Hey Leila, quick tip for you: if you want you ui element or picture to fit EXACTLY inside a cell – press and hold left ALT on your keyboard, then drag the image. This will snap the element / image to the closest corner of a cell. Do the same when re-sizing the element and the opposite corner will snap to the opposite corner of the cell.
with vba code it is possible I know that
is it possible, when we start typing in the combo box it will drop down list shown up automatically just like google. without using vba
What if there is another category under apps? How to add that?
Great Leila
I'd like to make my existing combo boxes dynamic and smart…what happens when the first select combo box is not from multiple columns but from from a single table div? Would love to see this example using only one column as the selection div (for example a software rating column, where no transpose is necessary) and then utilizing the offset and match lookup values in a different column based on the ratings – using existing table rows instead of columns… (insight please?)
great🙏
Thank you for all your help, Is there a way to have a dependent drop down attached to a multi column table
Hello Leila.
Thank a lot for the extraordinary work that you share with us.
I would like to know if you believe this approach works if you more than 2 characteristics. For instance inside the "Game Div." and Arcade you had another one, like modern of tradicional.
How would desing the information?
How can I make more than two combo box example 3 or 4
Index formula for define name is not working. While pressing the f9, it still shows the blank cells. Also could you explain the offset for this
hi i have two columns one is customer id and second is customer name. i have to create drop list for both can u guide how to do
Thanks a lot, just what I needed.
Thanks you Leila and S U! I'm gonna use the blank cells elimination to solve my long time problem.
very helpful for me!!! thanks Leila!
Hi Leila!
I am hoping you and your team can help me on a multi-dependent dropdown list problem I am working on.
I plan to populate a spreadsheet with numerical strength parameters for different species of construction lumber. Each species of lumber is broken down into similar categories, i.e., No.1, No.2, No.3. Within each category, numerical values can be obtained for 5 different design parameters.
The plan is to have 3 dropdown lists, each dependent on each other, that will generate an answer in a fourth cell. For example, the first list would contain 10 different species of lumber while the second list would include the 3 available categories (No.1, No.2, No.3) for each species. The third list would contain the 5 different types of design parameters to be selected. Based on the criteria of the three dropdown list items, a fourth cell would provide the numerical value that is sought out. The value generated in the fourth cell would be referenced in other calculations using different formulas.
Let me know if you have addressed similar issues in previous videos
Thanks!
Great video. Thanks Leila. Can you pls guide me if a drop down list associated to no of cells in a work sheet and drop down list updates in next cell not showing value selected in previous cell.
I am struggling with something what is very needed. I want table which calculates i.e. mortgage. The input is duration (in years, drop down list i.e.: 1-30y), interest and loan. Result is yearly payment. I want this table to be allowed to copy anywhere in the workbook, so I cannot use absolute addresses. The calculation uses button with macro solver. I want only needed cells to be populated – dependent on duration (in years). All objects (buttons+vba) should be inside that table for easy copying. If I copy i.e.: 3 tables, each button with vba has to know that it should calculate only it's own table. How to do it ?
And if there was an additional combobox, how would it be done?
Great video. Did you know that if you want the combo box to automatically fill the cell, after you choose the Combo Box place the cross-hair anywhere in the cell and hold down ALT and click. The box will draw itself to fill the cell.
Hi Leila, I'm in the middle of one of your courses and I'm loving it. Another formula I use that works is =OFFSET($N$4,0,0,COUNTIF($N$4:$N$18,"?*"),1) I know you're not a fan of offset but I use this formula in standard data validation (Source) and it works with a dynamic list.
I hope this may help someone
Nice.
I have a clarification.
It is limited to get only one record(i.e. revenue) at a time. I wish to view around 150 records(i.e. revenue) by selecting 150 combo box from the same data. Can you guide me?. Thanks in advance.
Hi Leila.. another great and useful video. Thanks for all the tips. Sharing something I created inspired from one of Mike Girvin's videos, here is another way to get a restricted drop-down list. Using your worksheet as a guide and so the cell addresses reference your data preparation table, you can use: =OFFSET($N$4,,,SUMPRODUCT(–ISTEXT($N$4:$N$18))). Assign this to the name "combo2" and you can use it just the same as your formula: =$N$4:INDEX($N$4:$N$18,MATCH("zzzzz",$N$4:$N$18,1)) assigned to the range name "combo" and entered into the Input Range field of the combo box or list box form controls. It can also be used as a Data Validation Source range either as a named range or even directly as a formula. For Data Validation, your formula can be used as a named range but not directly as a formula. I get a "you may not use reference operators" error when trying to use your formula directly as a list source for Data Validation. I picked this up from one of Mike G's lessons. He used an ISTEXT() test within another function to find the last item in a list. Keying on his idea, I realized I could use it with SUMPRODUCT to populate the height argument in OFFSET(), as my construct counts all the items, ignoring the blanks and returning the number of items in the list, which is exactly what is needed for the height argument. Understanding that OFFSET() is volatile, it might not be the best choice in certain circumstances. But, always nice to know multiple ways to solve a problem. Thanks again and Thumbs up!
PS – I just noticed that you used custom number formatting of ";;" in column N to suppress the display of zeros from the INDEX() formula that pulls the dependent list of items from your master table.. very clever!! Saves the need of wrapping another function such as IF() around INDEX() to remove the zeros.. nice!!
PSS – Another trick.. if you want to reset the CB APP Link value to 1 with each change of the Select Div drop-down selected, create the simple macro shown below and assign it to the Select Div Combo Box drop-down. In this way, each change of Select Div runs the macro and resets you to the first item of the Select App list vs. just taking the position of the last App selected from a prior Div selection.
Sub Reset_APP_Link()
Range("M4").Value = 1
End Sub
Thanks for the awesome solution! I love putting INDEX in a reference 🙂