Dependent validation list with table as source

[rickbisselink]

Hi all,

Could not find the solution elsewhere that combines my following questions, so I hope one of you has the solution!

I am trying to dependent validation list, where the source data is in one table (A1:C14). The table headers are generic and so cannot be used to refer to for the second validation list. The values in each of the columns in the table are not unique, so the validation lists need to have distinct values. Please find below my (test) worksheet, the validations would go into cells E2, F2 and G2, where F2 is dependent on E2 and F2 is dependent on E2 and F2.

Hope I'm making myself clear, thanks for your responses!

Kind regards,
Rick

Book1
	A	B	C	D	E	F	G
1	Day	Name	Product		Day	Name	Product
2	Monday	John	Apple
3	Monday	John	Pear
4	Monday	Peter	Orange
5	Monday	Peter	Banana
6	Monday	Steve	Kiwi
7	Tuesday	Karen	Orange
8	Tuesday	David	Mango
9	Tuesday	Jane	Tomato
10	Wednesday	Peter	Strawberry
11	Wednesday	Ryan	Kiwi
12	Wednesday	Karen	Lemon
13	Wednesday	Anne	Raspberry
14	Wednesday	Tom	Lemon
Sheet1

 

[MickG]

Try this:-
sheet1 of this example contains a Change Event for Range("E2:G2"), with a "Workbook _open" event to provide Range("E21" ) initial Validation list.

https://app.box.com/s/rpmms8tsfotsv16twrenn4enr7sgm0gd

 

[tyija1995]

Hi rick,

You could create 3 pivot tables
1) - Show Day in the Row labels
2) - Show Name in the Row labels
3) - Show Product in the Row labels

Then use a data validation on the pivot table list, as pivot tables group together duplicate values the list will be unique.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Row Labels[/TD]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Anne[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]David[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]Jane[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]John[/TD]
[TD]Lemon[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Karen[/TD]
[TD]Mango[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Peter[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ryan[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Steve[/TD]
[TD]Raspberry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tom[/TD]
[TD]Strawberry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD]Tomato[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

oops !!
The previous link was incorrect try this:-

https://app.box.com/s/cbfxq813lvfe898793xgoomujztr9nvm

 

[rickbisselink]

Thanks Mick, that works like a charm! Is VBA the only way to do this (in case a user has macro restrictions due to security settings)?

Tyija, in order to make your solution work, the contents of second pivot table should be dependent on the first chosen value. How do you picture this?

Rick

 

[tyija1995]

Hi Rick,

You could edit the 2nd pivot table to show Day and then Name so in your example it would look like this:

[TABLE="width: 100"]
<tbody>[TR]
[TD]Names[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[/TR]
[TR]
[TD="align: center"]Peter[/TD]
[/TR]
[TR]
[TD="align: center"]Steve[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD="align: center"]David[/TD]
[/TR]
[TR]
[TD="align: center"]Jane[/TD]
[/TR]
[TR]
[TD="align: center"]Karen[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD="align: center"]Anne[/TD]
[/TR]
[TR]
[TD="align: center"]Karen[/TD]
[/TR]
[TR]
[TD="align: center"]Peter[/TD]
[/TR]
[TR]
[TD="align: center"]Ryan[/TD]
[/TR]
[TR]
[TD="align: center"]Tom[/TD]
[/TR]
</tbody>[/TABLE]

I guess it would depend how often you're adding to the table though.

 

[MickG]

Try this:-

https://contextures.com/xlDataVal02.html

 

[rickbisselink]

Mick,

One more question: how would the VBA code change if the validation were not just in one row (E2:G2), but in multiple rows (E2:G2, E3:G3, E4:G4 etc.)?

 

[MickG]

Try this:-
https://app.box.com/s/exmvx3z3axawe09iqw9g9b55krk0yv9g
NB:- The validation boxes are available in "E2:G10", if you want to increase this range you will need to alter the Ranges "E2:E10" in the "Workbook Open" Event, and Range("E2:E10" and "F2:F10") and the sheet code "Change Event".

 

[rickbisselink]

Thanks again Mick!