Lists based on a Table

[iansouster]

Hi,

Hope someone can help me out there as I've been scratching my head with this problem all morning.

I have a table of information (see below) and I want to be able to create some drop down lists.

Vehicle	Make	Colour
Car	Ford	Red
Van	Peugeot	Green
Lorry	DAF	White
Car	Ford	Blue
Lorry	Scania	Grey
Car	Vauxhall	Green

What I want to do is in Cell 1 have a drop down list so I can pick a Vehicle, once the vehicle is picked, in Cell 2 I want to be able to pick from the available Makes for that Vehicle type, and then finally in Cell 3 to pick the available Colour.

For example:
If I was to pick Car in Cell1 the options in the drop down list for Cell 2 would be Ford and Vauxhall (no duplicates). After picking Ford in Cell 2 the options in Cell 3 would be Red and Blue.

I'm not sure if this is even possible but I know there are some very smart people on this Forum!

Thanks

Ian

 

[Fluff]

What version of Excel are you using?
You can modify your account profile to show this in your mini profile, which saves members having to ask.

 

[iansouster]

It's 365 on Windows - will look to alter my profile

 

[Fluff]

Ok, thanks for that.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Vehicle	Make	Colour		Vehicle	Make	Colour
2	Car	Ford	Red		Car	Ford	Red		Vehicle	Car
3	Van	Peugeot	Green		Van	Vauxhall	Blue		Make	Ford
4	Lorry	DAF	White		Lorry		Green		Colour	Red
5	Car	Ford	Blue
6	Lorry	Scania	Grey
7	Car	Vauxhall	Green
8	Car	Ford	Red
9	Van	Peugeot	Green
10	Lorry	DAF	Blue
11	Car	Ford	Green
12	Lorry	Scania	Grey
13	Car	Vauxhall	Green
14
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
F2:F3	F2	=UNIQUE(FILTER(B2:B100,A2:A100=J2))
G2:G4	G2	=UNIQUE(FILTER(C2:C100,(B2:B100=J3)*(A2:A100=J2)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J2	List	=$E$2#
J3	List	=F2#
J4	List	=G2#

 

[iansouster]

Works perfectly - Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.