Dynamic Dependent Drop Down Lists

[FluffySlipper]

Hi, I am having some trouble with dynamic dependent drop down lists. I am trying to create drop downs that will eventually allow easy costing of holidays. My drop downs are Country, Location, Operator, Venue etc. The problems I have are:
1) Not all the values are being returned by the SORT(Unique(Filter formula (there are no blanks or zeros in the data set), i.e. Only Locations for Zimbabwe are being returned
2) The drop downs that I have working are not dependent, i.e. I can select Botswana and it gives me Locations in Zimbabwe, whereas I only want the options for Botswana, if that is what is selected under Country.

I've uploaded mini sheets of my base data, the unique lists and the drop down lists. Hopefully this is helpful, but happy to post a link to the file if that would be easier. I would be very grateful for any assistance.

Costing Spreadsheet.xlsx
	A	B	C	D	E	F
1	Country	Location	Operator	Venue Name	Accommodation Options	Period
5	Zimbabwe	Hwange	African Bush Camps	Somalisa Acacia - Hwange	2 Luxury Tented Suites, 2 Family Tented Suites	April
17	Zimbabwe	Hwange	African Bush Camps	Somalisa Expeditions - Hwange	5 Luxuary Tents, 1 Family Tent	April
29	Zimbabwe	Hwange	African Bush Camps	Somalisa Signature- Hwange	7 Luxuary Tented Suites	April
41	Zimbabwe	Hwange	African Sun	Hwange Safari Lodge - Hwange	Deluxe Room	April
53	Zimbabwe	Hwange	African Sun	Hwange Safari Lodge - Hwange	Deluxe Room	April
65	Zimbabwe	Hwange	African Sun	Hwange Safari Lodge - Hwange	Deluxe Suite	April
77	Zimbabwe	Hwange	African Sun	Hwange Safari Lodge - Hwange	Deluxe Suite	April
89	Zimbabwe	Hwange	African Sun	Hwange Safari Lodge - Hwange	Executive Suite	April
101	Zimbabwe	Hwange	African Sun	Hwange Safari Lodge - Hwange	Executive Suite	April
113	Zimbabwe	Hwange	Wild Expiditions	Camp Hwange	Full Board	April
125	Zimbabwe	Kariba	African Bush Camps	Bumi Hills - Kariba	8 Lakeview Suites, 1 Interleading Suite	April
137	Zimbabwe	Kariba	African Bush Camps	Bumi Hills Baobab Villa - Kariba	1 Baobab Villa	April
149	Zimbabwe	Kariba	African Sun	Caribbea Bay - Kariba	Standard (Deluxe Suite)	April
161	Zimbabwe	Kariba	African Sun	Caribbea Bay - Kariba	Standard (Executive Suite)	April
173	Zimbabwe	Kariba	African Sun	Caribbea Bay - Kariba	Standard (Executive Suite)	April
185	Zimbabwe	Kariba	African Sun	Caribbea Bay - Kariba	Standard Room	April
197	Zimbabwe	Mana Pools	African Bush Camps	Kanga Expeditions - Mana Pools	4 Luxuary Tents, 1 Family Tents, 1 Honeymoon Tent	April
209	Zimbabwe	Mana Pools	African Bush Camps	Nyamatusi Mahogany - Mana Pools	2 Luxury Tented Suites, 2 Family Tented Suites	April
221	Zimbabwe	Mana Pools	African Bush Camps	Nyamatusi Signature - Mana Pools	6 Luxury Tents	April
233	Zimbabwe	Mana Pools	African Bush Camps	Zambezi Expeditions - Mana Pools	6 Luxury Tents	April
245	Zimbabwe	Motobo	African Bush Camps	Khayelitshe - Motobo National Park	4 Luxury Suites	April
257	Zimbabwe	Vic Falls	African Sun	Elephant Hills - Vic Falls	Deluxe Suite	April
269	Zimbabwe	Vic Falls	African Sun	Elephant Hills - Vic Falls	Executive Suite	April
281	Zimbabwe	Vic Falls	African Sun	Elephant Hills - Vic Falls	Presidential Suite	April
293	Zimbabwe	Vic Falls	African Sun	Elephant Hills - Vic Falls	Standard (Falls & Spray View)	April
305	Zimbabwe	Vic Falls	African Sun	Elephant Hills - Vic Falls	Standard (Game, Golf & River Sunset)	April
314	Zambia	Livingston	aaa	aaaa		April
315	Zambia	Livingston	ab	abb		April
316	Namibia	b	bb	bbb		April
317	Namibia	bb	bc	bcc		April
318	Botswana	c	cc	ccc		April
Accommodation

Costing Spreadsheet.xlsx
	O	P	Q	R	S	T
1	Country	Location	Operator	Venue Name	accommodation Options	Period
2	Botswana	Hwange	African Bush Camps	Bumi Hills - Kariba	5 Luxuary Tents, 1 Family Tent	April
3	Namibia	Kariba	African Sun	Bumi Hills Baobab Villa - Kariba		August
4	Zambia	Mana Pools	Wild Expiditions	Kanga Expeditions - Mana Pools		December
5	Zimbabwe	Motobo		Khayelitshe - Motobo National Park		February
6		Vic Falls		Nyamatusi Mahogany - Mana Pools		January
7				Nyamatusi Signature - Mana Pools		July
8				Somalisa Acacia - Hwange		June
9				Somalisa Expeditions - Hwange		March
10				Somalisa Signature- Hwange		May
11				Zambezi Expeditions - Mana Pools		November
12						October
13						September
14
15
16
Accommodation

Cell Formulas
Range	Formula
O2:O5	O2	=SORT(UNIQUE(Accommodation[Country]))
P2:P6	P2	=SORT(UNIQUE(FILTER(Accommodation[Location],Accommodation[Country]=$A20)))
Q2:Q4	Q2	=SORT(UNIQUE(FILTER(Accommodation[Operator],Accommodation[Location]=$B20)))
R2:R11	R2	=SORT(UNIQUE(FILTER(Accommodation[Venue Name],Accommodation[Operator]=$C20)))
S2	S2	=SORT(UNIQUE(FILTER(Accommodation[Accommodation Options],Accommodation[Venue Name]=$D20)))
T2:T13	T2	=SORT(UNIQUE(FILTER(Accommodation[Period],Accommodation[Venue Name]=$D20)))
Dynamic array formulas.

Costing Spreadsheet.xlsx
	A	B	C	D	E	F
18	Accommodation
19	Country	Location	Operator	Venue Name	Accommodation Options	Period
20	Zimbabwe	Vic Falls	African Sun
21
22
23
24
25
26
Summary

Cells with Data Validation
Cell	Allow	Criteria
A20	List	=Accommodation!$O$2#
B20	List	=Accommodation!$P$2#
C20	List	=Accommodation!$Q$2#
D20	List	=Accommodation!$R$2#
E20	List	=Accommodation!$S$2#

 

[alansidman]

See if this helps