Home

Dependent Drop-down List- Formula for only showing cells with data rather than blanks

%3CLINGO-SUB%20id%3D%22lingo-sub-354885%22%20slang%3D%22en-US%22%3EDependent%20Drop-down%20List-%20Formula%20for%20only%20showing%20cells%20with%20data%20rather%20than%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354885%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20creating%20a%20template%20that%20contains%20multiple%2C%20dependent%20drop-down%20lists.%20If%20you%20choose%20a%20certain%20thing%20in%20the%20first%20list%2C%20it%20will%20only%20show%20options%20related%20to%20that%20choice%20in%20the%20second%20drop-down%2C%20and%20so%20on.%20The%20items%20in%20my%203rd%20list%20all%20contain%20a%20different%20number%20of%20populated%20cells.%20When%20I%20set%20up%20the%20formula%2C%20it%20captures%20the%20whole%20table%20rather%20than%20just%20showing%20the%20cells%20with%20data%20based%20on%20what%20column%20it%20is.%20I%20end%20up%20with%20blanks%20as%20choices%20in%20the%203rd%20drop%20down.%26nbsp%3B%20Based%20on%20what%20is%20chosen%20in%20the%202nd%20drop-down%2C%20how%20do%20I%20only%20see%20those%20choices%20in%20the%203rd%20drop-down%20rather%20than%20blanks%20as%20well%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20current%20formula%20and%20the%20example%20is%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX('Drop-Down%20Backup'!%24E%244%3A%24S%2451%2C%2CMATCH(%24B%245%2C'Drop-Down%20Backup'!%24E%243%3A%24S%243%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354885%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355663%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20Drop-down%20List-%20Formula%20for%20only%20showing%20cells%20with%20data%20rather%20than%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355663%22%20slang%3D%22en-US%22%3EYou%20just%20have%20to%20rename%20the%20Data%20sheet%20and%20the%20workbook%20name%20in%20me%20uploaded%20file.%20The%20layout%20of%20your%20data%20and%20drop-down%20list%20must%20be%20strictly%20in%20accordance%20with%20mine%20so%20that%20the%20formulas%20will%20deliver%20the%20expected%20results.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355495%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20Drop-down%20List-%20Formula%20for%20only%20showing%20cells%20with%20data%20rather%20than%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355495%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20still%20having%20trouble%20getting%20it%20to%20work%20with%20how%20my%20data%20is%20set%20up.%20I%20tried%20removing%20the%20tables%20so%20the%20backup%20data%20was%20just%20text%2C%20but%20the%20formula%20still%20isn't%20working.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355193%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20Drop-down%20List-%20Formula%20for%20only%20showing%20cells%20with%20data%20rather%20than%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355193%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Lindsay%2C%26nbsp%3B%3C%2FP%3E%3CP%3EHereto%20attached%20is%20the%20file%20with%20the%20dynamic%20dependent%20drop-down%20list%20you%20need.%20Aside%20from%20your%20given%20data%2C%20I%20added%20Beverages%20as%20a%20category%2C%20along%20with%20the%20corresponding%20subcategories%20and%20items%2C%20to%20the%20Data%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3EBeing%20dynamic%2C%20you%20can%20add%20(or%20delete)%20categories%2C%20subcategories%2C%20and%20items%20in%20the%20Data%20sheet%20without%20any%20required%20modification%20in%20the%20formula.%20Download%20it%20and%20inform%20me%20of%20your%20thoughts%20about%20it.%3C%2FP%3E%3CP%3ECheers%2C%26nbsp%3B%3C%2FP%3E%3CP%3ETwifoo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354887%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20Drop-down%20List-%20Formula%20for%20only%20showing%20cells%20with%20data%20rather%20than%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354887%22%20slang%3D%22en-US%22%3EPlease%20refer%20to%20my%20reply%20here%20for%20an%20idea%20of%20a%20dynamic%20dependent%20drop-down%20list%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FData-validation-lists-flow-chart%2Fm-p%2F352133%23M25722%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FData-validation-lists-flow-chart%2Fm-p%2F352133%23M25722%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-731056%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20Drop-down%20List-%20Formula%20for%20only%20showing%20cells%20with%20data%20rather%20than%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731056%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3EI%20have%20also%20been%20looking%20for%20this%20solution%20for%20a%20long%20time.%3C%2FP%3E%3CP%3EI%20have%20the%20following%20questions%3A%3C%2FP%3E%3COL%3E%3CLI%3EHow%20do%20I%20add%20more%20categories%3F%3C%2FLI%3E%3CLI%3EHow%20do%20I%20change%20your%20input%20message%3F%3C%2FLI%3E%3C%2FOL%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EJohnny%3C%2FP%3E%3C%2FLINGO-BODY%3E
LindsayP
New Contributor

Hello, 

 

I am creating a template that contains multiple, dependent drop-down lists. If you choose a certain thing in the first list, it will only show options related to that choice in the second drop-down, and so on. The items in my 3rd list all contain a different number of populated cells. When I set up the formula, it captures the whole table rather than just showing the cells with data based on what column it is. I end up with blanks as choices in the 3rd drop down.  Based on what is chosen in the 2nd drop-down, how do I only see those choices in the 3rd drop-down rather than blanks as well?

 

Here is my current formula and the example is attached. 

=INDEX('Drop-Down Backup'!$E$4:$S$51,,MATCH($B$5,'Drop-Down Backup'!$E$3:$S$3,0))

 

Thank you! 

5 Replies

Hello Lindsay, 

Hereto attached is the file with the dynamic dependent drop-down list you need. Aside from your given data, I added Beverages as a category, along with the corresponding subcategories and items, to the Data sheet. 

Being dynamic, you can add (or delete) categories, subcategories, and items in the Data sheet without any required modification in the formula. Download it and inform me of your thoughts about it.

Cheers, 

Twifoo

Hello, 

 

I'm still having trouble getting it to work with how my data is set up. I tried removing the tables so the backup data was just text, but the formula still isn't working. 

You just have to rename the Data sheet and the workbook name in me uploaded file. The layout of your data and drop-down list must be strictly in accordance with mine so that the formulas will deliver the expected results.
Highlighted

Thank you!

I have also been looking for this solution for a long time.

I have the following questions:

  1. How do I add more categories?
  2. How do I change your input message?

Regards

Johnny

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies