This Excel Dependent Combo Box Solves an Annoying Problem (you've probably had this)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/dependent-combobox-file

[@JanKowalski-hk5cg]

Dear Ms Gharani, this video is the answer to creating dependent drop down lists and making drop down arrows visible for users. It took me 5 hours of searching the YT until I finally found your video with this awsome solution. Many Thanks.

[@eldarikus]

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.

[@mikeboote699]

Excellent tip. Don't lock aspect ratio in the control format though!

[@SJExcel]

After searching a lot finally I got what I want. I would like to thank you for all your videos. Not everyone can teach, I must say that you have the flair of that. Karachi, Pakistan

[@gauravvrasttogi]

I searched on You Tube and hey YOU are the ONLY ONE giving this solution. Nobody else has found this solution probably !!. You are so amazing. I am a big fan of You..Oops..your Excel skills.. :)

[@LeilaGharani]

I'm glad to hear that :)

[@Mohammad3000100]

Thanks for your video and the most inspiration part for me is the last one about list box it's like a very direct query. You always do a stunning things with excel I hope you happy new year.

[@cellardoor10]

This video helped me IMMENSELY in creating a form to fill out for work. THANK YOU!!

[@LeilaGharani]

Wonderful!

[@karencrouch5671]

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.

[@LeilaGharani]

That's SO cool! No I didn't know that! Thanks for sharing :)

[@JohnnyTidmore]

Sweet tip!

[@excelisfun]

Thanks Leila and S U!!! Great solution : )

[@pvv_live2226]

Cool ! Very usefull, thank's Leila!

[@LeilaGharani]

Thank you Mike :)

[@techselec]

Hey Leila! Thanks for the wonderfull trick. Have prepared the date by using your method. Only challenge is which curretly i am facing, have to take a sum of entire value. Example:- By default, Sum of all the products included all the cities should be there, if i click region than branch wise total appear. But in any case, if have to see the total of all the sku, there is no option. I hope you can highlight the same on this as well like you solve dependent drop down list issue.

[@ricos1497]

Hi Leila. I just use conditional formatting (formula below) in the cell to change the font to the cell background colour, which works perfectly. =ISNA(VLOOKUP($G$56,INDEX($A55:$C70,,MATCH($G$55,$A$55:$C$55,0)),1,FALSE)) would return TRUE in your example at the beginning of the video (I think I got the cell refs correct) so you could base your conditional format on that. Still, a good illustration of combo boxes nevertheless, which I find a lot nicer on the eye than data validation lists. The great thing about them is that they're not restricted by cell width, which means you can have a nice report or whatever directly below without enforcing column sizes on the drop down or the report. Thanks!

[@sharmiladevikrishnamurthy3490]

Thanks Leila, this video is of great use, could you please post one more video populating third list box, basis the 2nd list box value

[@steveparrott9762]

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

[@LeilaGharani]

Thank you Steve! That's a great formula too. Many thanks for sharing.

[@momensharkas1473]

DR liela is the best on ms office . amazing talent

[@michaelshultz5584]

Great walk through of a complex subject.

[@LeilaGharani]

Thank you Michael - Glad you find it easy to follow :)

[@lonski-marlonperina1909]

Wow! Amazing tutorial. I'm gonna try this. Thanks for sharing your knowledge Leila. More power!

[@LeilaGharani]

Thank you Marlon - glad you find it useful! Thank you for your support.

[@munneysh]

Great video Leila. Now I wanna learn the VBA solution.

[@rockhardinc8510]

Thank you for all your help, Is there a way to have a dependent drop down attached to a multi column table

[@ZeshanAhmedSheikh]

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.

[@mihajlovicrus]

Hi Leila, Thanx for great video! How can I choose multiple items in dependent list below and calculate Profit for multiple items? Thank you

[@dnyeckjr]

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

[@davewebster160]

zzzzzz? That’s some old-school ASCII magic right there. Love it!! 👍

[@ibrar736]

Thank you so much for this awesome video. This is very much helpful.

[@NZAbbasi]

Thank you- You've made me to fall in love with excel all over again (sounds nerdy!)

[@LeilaGharani]

Sounds good :)

[@NZAbbasi]

@@LeilaGharani - using your tips, I have used Active X Combo the same. The issue I had was to change the font size in the drop down menu. Only available option is through the use of VBA which I initially wanted to avoid. It is still not defaulting back to the first choice in the list, but achieves the purposes I wanted it to. Thank you again.

[@shayanchakraborty5761]

Very useful video as usual..but I guess I am missing a point somewhere since I am getting a zero (0) as an option in the dependent drop-down list. Can you please elaborate a bit on how we are copying the Index formula across the column? Thanks

[@marcelcaous8786]

I am trying to get the name manager formula (9:10 min) to work with numbers in the list instead of text lists. Any tips on how this can be achieved?

[@sandipgumtya130]

Hi Leila, Thanks a lot for this. I am getting few trailing zeros in the "App Content" column. I know it is because of the empty cells. but i don't want these to appear in the dependent dropdown. How to treat this?

[@shayanchakraborty5761]

Exactly my query..I am trying using different ways but the trailing zeroes remain whereas in the practice sheet there is no such errors

[@hamidrezahosseini7259]

Hi, follow below path: File>option>Advanced>Display Options for this worksheet uncheck show a zero in cells that have zero value enjoy

[@maciejsergiel6330]

@@hamidrezahosseini7259 Thanks!

[@aebrinkley]

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.

[@fvelez78]

Hi Leila, this solution is great, but a few practical if you need to do this whith 100 depedent lists. Exist any solution more efficient? Thank.

[@hamidrezahosseini7259]

Thanks for valuable video. for me index function in App content column return 0 for blank cells. how can fix it?

[@hemanthnani5052]

@Leila Gharani. Everytime you copy and paste the Index-match formula down to multiple rows, I see that the cells that doesn't have values to display, always display blank in your videos, but for me it displays "0". Please help me with this if there is any settings I need to make?

[@remcovankempen422]

Again a perfect solution, for a problem I just accepted before 👍🏻

[@ninoniina]

Hello, thank's for a video, but I have a question: I did the same thing, but whenever i choose another filed in combo box, index cell doesn't change, only if i'll click, couldn't find any information on that.

[@sukhpreetsingh3082]

Hi. Do u have any video to learn on the meaning of writing formula

[@arulthangavelu3639]

Excellent teaching i undestood & updated my skills

[@saileshmutreja9311]

Always something new... Thanks a lot for your hard work...

[@felipemateini4058]

I've learned a lot with yours videos.... And yet I can improve my english!!! thanks and congratulations!!!!

[@saiprakashpawar5825]

Thanks Leila its really use full for my work, Kindly let me know how to add more than 4 dependent combo boxes

[@christinelowery2415]

Great information. Do you have a way to copy it down several rows to be dependent upon what is chosen in each row?

[@ExcelExciting]

Thanks Lelia... nice video 👍🏻

[@LeilaGharani]

Thank you Faraz :)

[@satishgopal2406]

Thank you 🙏 was very easy to follow...!!

[@siddharthsaini9972]

Nice lelia. You are online best teacher

[@MalinaC]

Thanks for the awesome solution! I love putting INDEX in a reference :)

[@LeilaGharani]

You're welcome Malina - Agree - Index has so many faces :)

[@ajexcel]

This is really useful thanks Leila👍

[@LeilaGharani]

:) you're welcome Ajay.

[@rhueoflandorin]

great video!! very well articulated. subscribed :)

[@____----____----____]

Brilliant, just what i needed in combination with the unique values list. Now i can create a smart form, without using vba, oledb and sql, which makes my form non-macro!

[@SholaDsdg]

Thanks you Leila and S U! I'm gonna use the blank cells elimination to solve my long time problem.

[@sdsagar742]

Nicely explained, Good video thank you Leila for updating us

[@sgtpepper634]

So happy I found a way to do this without VBA!

[@joeybantog9917]

very helpful for me!!! thanks Leila!

[@krn14242]

Thanks Leila and SU. On to the VBA solution now. :)

[@therathores]

Hi, Thanks for this video, I have few queries 1. Is it possible to add "All" as well for both "division" as well as for "App content". 2. Is it possible to put these 2 in checkbox, so that If I select "Utility Div" from dropdown check box then I should be getting particular app list in dropdown check box? Thanks.

[@peterjohnsarador6194]

Thanks Leila, I prefer the combo box approach. 👍

[@LeilaGharani]

My pleasure Peter. Thanks for the thumbs up.

[@michaelsmith935]

Very helpful!

[@sachinsharma-de3vj]

can I put a IF formula using the Cell Link as reference, for example if cell reference =1, throw value as False

[@sebofpv3219]

AWESOME , thank you Leila

[@wayneedmondson1065]

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

[@LeilaGharani]

Thank you Wayne! These are great tips & tricks. I like that ISTEXT SUMPRODUCT combo! Very clever. I also like the simple VBA code code for resetting. Thank you for sharing.

[@aebrinkley]

HI Wayne, regarding your PSS about resetting using a small macro or vba. I am creating a form that will be added as a sheet to several other templates used by multiple other countries. It has been requested not to use macros or vba as the file has to be saved specifically as macro enabled. Is there a way to reset without using macro/vba?

[@supriyodam3918]

Can you update a proper gnatt chart for construction activity work

[@JimFikes]

Another great video, Leila.

[@LeilaGharani]

Thank you Jim :)

[@norsyafawatishamsahaldin7026]

Hai Ms Gharani thank you very much for your video. I have tried it works . Just for dependeant dropdwon list, the empty becomes 0 0 , how to remove this and unseen in drop down list Thank you

[@venkatbabu5866]

Thanks mam. You are my excel guru.

[@lephterisp]

Mrs Gharani thank you very much for everything that you give us.You help me a lot with your videos.I would like to ask you something! I took a picture of a library and put it as background in Excel so from an other sheet i named the books (in certain cells in the photo) with hyperlink to the cells..The problem is i cant manage to prevent a picture from being tiled ..I becomes confusing ..is there a way to prevent it from multiply itself?? Thanks in advance..

[@Sam-wt6iy]

Super duper useful

[@yulinliu850]

Great solution! Many thanks!

[@malcolmboswell9717]

Excellent tip, thanks!

[@indianindian5936]

Hi, Thanks for the great video, I came across an issue, that is explained below: once i select "Productivity Div" from Combo-box1 and select "Rehire" from combo-box2 cell C4 value is 14 which is right. now when i change option in Combo-box-1 to "Game Div" or "Utility Div" combo-box2 reset the value whereas C4 remains the value as 14. kindly give me a solution for this. NOTE: I am using the offset function in the name manager for defining the second combo box. Appreciate your response. Thanks

[@chuckgrigsby9664]

Is there a way to have the second combo box default to the top of its list rather than to the value corresponding to the position from the previous selection in that combo box?

[@tiagopaiva6885]

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?

[@26SSD]

Nice video.. very useful thanks Leila :)

[@elizabethafranewaaabayatey71]

Great job Leila, i have built my form perfectly with this, i now want to add a submit button which will populate my data, any ways around that?

[@ExcelForBeginners.]

Very explanatory,

[@brightaslight624]

Thank you very much for this video. It is a very informative video. I have a question. If my index values is in a different sheet. How can I pull the data from that index to my current sheet? For example, my index value or (the list of the data) is in sheet No. 1 and my working sheet is sheet No. 2 and I want to use a combo box or (a drop down list) to pull the data from sheet No.1 to sheet No.2 , how can I do this?

[@alexvinotha4707]

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.

[@مقدادحديد]

you are wonderful queen thanks for all you do

[@LeilaGharani]

Thank you for your continuous wonderful support.

[@alfredobellagamba5028]

Great!! Very useful, thanks Leila :)

[@sleeksjay781]

Can you please add the VBA Approach - I learned from another source to use ThisWorkbook.RefreshAll. So I incorporated it with a change event- this should refresh all the links when a particular values in a Specified range is changed.

[@johnborg6005]

That was Great Leila!!! :) :)

[@LeilaGharani]

Thank you John :)

[@zerberoo9937]

Hey. It took me a whole day to find you and this tutorial. I'm putting together a sheet for my Minecraft-Mods, but instead of a table I wanted to use a sheet with nice combo boxes, so it looks fancy. I followed every step of your tutorial... But I got some columns with just 5 or less entries and some with like 10.... After the F4 and the ctrl+Enter part - I got some zeros in a few spots. This carries over to the combobox, if I mark the column with the empty spaces. Do you know how I could erase them, so that the combo box isn't full of zeros or blank entries? Would be very helpful. And I really hope, that you read comments under older videos. Have a nice and sunny day :D

[@akrampasha]

Its very useful, Thank you...

[@paganartgiyan]

Thank you :)

[@muraliiyer7850]

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

[@alirezamogharabi8733]

great idea, thank you Leila.

[@Bengelwes]

Hello Leila... this video is great. However I had a question about the Name Manager. You stated around minute 5, that you can use names indirectly with the Combo Box. I tried this, but if I use INDIRECT, the Combo Box does not allow this formula and removes it ones you hit OK. Also I cannot use "Add to formule" in the Name manager. Any idea of how to use it? Regards Wes

[@harrietdavison4283]

Hello! I took your excel dashboard course and I have a question on the first dashboard. I am looking to have the data change based on month and division. Is this possible? So for example, if I selected Jun and Game, the date would pull from the game apps during the month of June. Please let me know!

[@1gopalakrishnarao]

Thanks a lot. Very informative, useful,.

[@1gopalakrishnarao]

Thank You. Really you are a GOD SENT EXCEL TEACHER to the Excel Lovers

[@anishpillai]

Thanks su and thanks leila. Can I save the file is xlsx format if I use form controls or do I have to save it in macro type xlsm?

[@LeilaGharani]

Yes - you can save in the usual xlsx format :) That's a good advantage of this solution too. No VBA needed.

[@colinhuntley9729]

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

[@AdiCalin]

Excellent!

[@brightaslight624]

Thank you very much for this video. It is very informative. I have a question. If my index values is in a different sheet. How can I create a search drop-down list that pulls the data I'm looking for from a different sheet? But, it should be an auto-complete search once I put the initials it completes itself? For example, my list of values is in sheet No. 1 and my working sheet is in sheet No. 2 and I want to use a search drop-down list that pulls the data from sheet No.1. How can I do this? Show less Reply

[@charrizdomingo986]

Thank you so much

[@LeilaGharani]

You're very welcome. I'm glad you like the video.

[@mclaughlingroup1]

Leila, Can you make a tutorial (or point me towards one) that shows how to make a combobox that when you start typing a value (from a list etc) that you can hit enter or tab and it accepts the value rather than clicking with the mouse.

[@satishsharma3875]

Hi Leila - here you have unique values in each column(Prod, Game and Utility), my sheet is having multiple values in each column, so my combo box showing same values many times, how do i show a unique list in my combo box? thanks in advance

[@gershonfranklynaguze2624]

I have a database with regions and their districts with district capital. if i click on combobox1, it shows the regions, combobox2 displays district of combobox1. however i canot get the district capital in the textbox. please help me

[@keremio9120]

thanks!

[@mfneyaz]

thank you very much!

[@jrobinson1976]

Do you have a video explaining the difference between data validation list vs combo box and when to use which one? Pro's/Con's etc.

[@LeilaGharani]

Not yet - I cover the basics of combo box here: kzbin.info/www/bejne/bIDXeYCaiaiYpbM but I don't have one that explains the difference. Good idea for a video. Thanks.

[@pinkeishz9106]

Super like👌

[@ravi77003]

hii leila, can u plz explain how to link combobox with chart in dashboard?? so that user can can get the flexibility to choose option from combobox, so that respective value can display. plz plz help me out

[@hongqirong9927]

Hi, Mr PK from ExcelExpert can help you out. 1) kzbin.info/www/bejne/ioSrlYqGg9CdiqM&ab_channel=PK%3AAnExcelExpert Part 1 Dynamic Variance Arrows Chart with checkbox 2) kzbin.info/www/bejne/b2OniaCvlJ2Fftk&ab_channel=PK%3AAnExcelExpert Part 2 Dynamic Variance Arrows With Slicer 3) kzbin.info/www/bejne/b5_RhH-gnbt6npo Dynamic Sales Score Meter Chart

[@technicalafghan-8564]

Great Leila

[@Meolimo]

Thank you Leila