Dependent dropdowns - with a twist

[cornflakegirl]

I have a spreadsheet with dropdowns that allow the user to slice the data they see. Let's say that they can select the store (All, Tesco, Sainsbury, Waitrose) and the product (All, Apples, Bananas, Custard). However, Tesco don't sell apples. Currently if they select Tesco and apples, they get a page full of errors, so I want to remove the option to select this combination. So, if Tesco is selected, I don't want apples to be in the Product dropdown. And, if apples is selected, I don't want Tesco to be in the Store dropdown. Is the best (only?) way to do this with VBA?

 

[machopicho]

In vba you should use two combobox and ensure that when Tesco is selected in combo1, in combo 2 insn't permitted loading of item apple and viceversa.

 

[cornflakegirl]

Yeah - I was just wondering if there was a named range way (or something else) that I hadn't thought of?

 

[sous2817]

Couldn't you create a named range called "Tesco" and in that range, just have the options they sell. And in your second dropdown formula use =INDIRECT(A1) (or whatever your first dropdown location is.

That way, when they select Tesco, they'd only be able to select the options that Tesco sells....


To get around the other part (selecting Apples and then selecting Tesco), I've used a worksheet change event to clear out the the first selection (location) when the 2nd selection (Item) is changed...you can make that as complex as you'd like (using a search to make sure that apples is a valid choice, etc.).

 

[sous2817]

This also seemed to work on my test sheet:

The data validation of the location list is:

=IF(ISERROR(INDIRECT($J$17)),fruit,INDIRECT($J$17))

fruit is the named range of what's available. J17 has the location.

Additionallly, each location is a named range on what's available to sell. So the idea is if J17 is blank, they get the compelte list. If they have a location selected, then the only thing that shows up is the items available to sell at the location they've selected....


let me see if I can clean up my test sheet a bit and post an image. It may help explain a bit better.

 

[sous2817]

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q
1	Named Range 'Fruit' in F2		Fruit	apples	Peanuts	All_Locations		LocationA	LocationB	LocationC	LocationD	LocationE
2	Peanuts		Apples	LocationA	Location C	LocationA		Apples	Apples	Apples	Peanuts	Peanuts
3			Peanuts	LocationB	Location D	LocationB				Peanuts
4	Location in F5			LocationC	LocationE	LocationC
5	LocationC					LocationD
6						LocationE
Sheet1

Excel 2007


Not sure if the above helps at all but...

H1:Q1 are the named ranges for the values below them.

The data validation formula in F2 is:

=IF(ISERROR(INDIRECT($F$6)),Fruit,INDIRECT($F$6))

The data validation formula in F6 is:

=IF(ISERROR(INDIRECT($F$2)),All_Locations,INDIRECT($F$2))

Seemed to work like you wanted...

 

[cornflakegirl]

Sous - I'm not sure I entirely follow yet, but are you saying that I can have two mutually dependent dropdowns? That would be perfect. Thank you very much for your help. I shall go and have a play.

 

[cornflakegirl]

It works! You're a genius! Thank you.

 

[sous2817]

HURRAY!! Glad you got it all worked out! Best of luck with the rest of your project.