Data validation - dynamic and also dependent on a particular column

[realdemigod]

Hi,
Please refer to the image in the link below. I’m trying to create a data validation that lets a user choose Product and Colour of a car from the Manufacturer.


Is there way to put some function in data validation list option so that it looks for Audi on the worksheet and takes the Product as the first list and then second data validation show the list of colours again the product types (Q7 and Q5)


For example data validation at A1 should show Q7 and Q5 and then at B1 should show the corresponding colours against each model for Audi. The range could be anywhere in the particular columns but the placements of the columns are fixed. So defining a name would be a challenge.

I can't use any macro. Is it possible with some if or some other function?

https://imgur.com/aevnBpg

Thanks

 

[Toadstool]

I can't see how you can build multiple LoV selections from the data list as Data Validation, List needs the dropdown entries to be contiguous.

 

[realdemigod]

I can't see how you can build multiple LoV selections from the data list as Data Validation, List needs the dropdown entries to be contiguous.

I don't know just exploring the possibilities.

 

[Toadstool]

I had a thought...
The Manufacturer is unchanged as it doesn't rely on any other factor, but can you give me a number of the absolute maximum Manufacturers you'll ever have?
The Product and Colour could be done horizontally with data extracted and formulae for each row of LoVs.
What is the maximum number of LoV selection rows?
What is the maximum number of Products for any one Manufacturer?
What is the maximum number of colours for a Product?

I want to have some limit as I'm afraid it may be slow with so many formulae but I can try.

 

[realdemigod]

That is a challenge, the data keeps changing, can't put maximum on minimum for any field

 

[Toadstool]

Oh! OK, so if I can't use horizontal tables for each row (for which I'd need to put some kind of range limit) and I don't want to resort to VBA then the best I can do is this...

The new values in column O use the active cell =CELL("row") to identify which row you're working on and then build the dynamic LoV's based on that Manufacturer. The wrinkle is that if you want to change an existing row entry you must re-select the Manufacturer first so it knows which row you're working on.

Here's the sheet with rows up to 1,000 allowed but you can easily change that and it should theoretically work up to 1 million... but I wouldn't expect a quick recalculation with that many rows.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	--- Selections ---				--- Data ---				--- Worker Columns ---
2					Product	Colour	Manufacturer					$L$4:$L$5	$M$4:$M$5	$N$4:$N$6	2
3	Manufacturer	Product	Select Colour		Q7	White	Audi		ManU	ManProd	ProdU	Man	Prod	Colors	BMW
4	Audi	Q5	Blue		Q7	Blue	Audi		6	Q7Audi		Audi	X3	Grey	X3BMW
5	BMW	X3	Magenta		Q7	Red	Audi		5	Q7Audi		BMW	X5	Magenta
6					Q7	Black	Audi		4	Q7Audi				Florescent
7					Q5	Blue	Audi		3	Q7Audi
8					Q5	White	Audi		2	Q5Audi
9					X3	Grey	BMW		1	Q5Audi
10					X3	Magenta	BMW		6	X3BMW	3
11					X3	Florescent	BMW		5	X3BMW	2
12					X5	Teal	BMW		4	X3BMW	1
13					X5	BabyBlue	BMW		3	X5BMW	3
14					X5	StarkWhite	BMW		2	X5BMW	2
15									1	X5BMW	1
16									0
17									0
Master

Cell Formulas
Range	Formula
L2:N2	L2	=ADDRESS(ROW(L4),COLUMN())&":"&ADDRESS(ROW(L4)+COUNTIF(L4:L44,"> ")-1,COLUMN())
O2	O2	=CELL("row")-COLUMNS($A$1:$C$3)
O3	O3	=INDEX($A$4:$A$9999,O2,1)
I4, I5:I17	I4	=COUNTIF($G3:$G$1000,G3)
J4, J5:J17	J4	=E3&G3
K4, K5:K17	K4	=IF(G3=$O$3,COUNTIF($J4:$J$1000,E3&G3),"")
L4, L5:L17	L4	=IF(COUNTIF(I:I,1)<ROWS(L$3:L3),"",INDEX(G$3:G$1000,AGGREGATE(15,6,ROW(I$4:I$1000)-ROW(L$3)/(I$4:I$1000=1),ROWS(L$3:L3))))
M4, M5:M17	M4	=IF(COUNTIF(K:K,1)<ROWS(M$3:M3),"",INDEX(E$3:E$1000,AGGREGATE(15,6,ROW(J$4:J$1000)-ROW(M$3)/(K$4:K$1000=1),ROWS(M$3:M3))))
N4, N5:N17	N4	=IF(COUNTIF($J$4:$J$1000,$O$4)<ROWS(N$3:N3),"",INDEX(F$3:F$1000,AGGREGATE(15,6,ROW(N$4:N$1000)-ROW(N$3)/(J$4:J$1000=$O$4),ROWS(N$3:N3))))
O4	O4	=INDEX($B$4:$B$9999,O2,1)&INDEX($A$4:$A$9999,O2,1)

Cells with Data Validation
Cell	Allow	Criteria
A4:A1000	List	=INDIRECT($L$2)
B4:B1000	List	=INDIRECT($M$2)
C4:C1000	List	=INDIRECT($N$2)

 

[realdemigod]

Thanks Toadstool, let me work it out and get back to you.

 

[realdemigod]

Thanks Toadstool, this seems to be working great! I will implement on my large data set and hopefully should not have any problem. Thanks again!