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

 

[Fluff]

@Toadstool
When using < or > symbols you need to add a space either side, otherwise the board thinks that you are posting HTML code.

 

[Toadstool]

You're welcome!

Well we already have the list of possible colors in column N starting at row 3. You can put in N1
=ADDRESS(ROW(N3),COLUMN())&":"&ADDRESS(ROW(N3)+COUNTIF(N3:N43,"> ")-1,COLUMN())
so we'll get the address range of any colors with a non-blank entry.

Now widen column M and in M2 we'll enter the heading "Select color".
Select M3 and do a Data, Data Validation, List with a source of =INDIRECT($N$1)

So that gives you the selected Manufacturer, Product and Color in cells K3, L3 and M3.

 

[Toadstool]

@Toadstool
When using < or > symbols you need to add a space either side, otherwise the board thinks that you are posting HTML code.

Aaah! Thanks @Fluff. Let me check in this reply

=IF(COUNTIF(E:E,1) < ROWS(H$2:H2),"",INDEX(C$3:C$20,AGGREGATE(15,6,ROW(E$3:E$20)-ROW(H$2)/(E$3:E$20=1),ROWS(H$2:H2))))

=IF(COUNTIF(G:G,1) < ROWS(I$2:I2),"",INDEX(A$3:A$20,AGGREGATE(15,6,ROW(F$3:F$20)-ROW(I$2)/(G$3:G$20=1),ROWS(I$2:I2))))

=IF(COUNTIF($F$3:$F$20,L$3&K$3) < ROWS(N$2:N2),"",INDEX(B$3:B$20,AGGREGATE(15,6,ROW(N$3:N$20)-ROW(N$2)/(F$3:F$20=L$3&K$3),ROWS(N$2:N2))))


Yep! Looks good in Preview (well, as good as any formula that long can look).

 

[realdemigod]

You're welcome!

Well we already have the list of possible colors in column N starting at row 3. You can put in N1
=ADDRESS(ROW(N3),COLUMN())&":"&ADDRESS(ROW(N3)+COUNTIF(N3:N43,"> ")-1,COLUMN())
so we'll get the address range of any colors with a non-blank entry.

Now widen column M and in M2 we'll enter the heading "Select color".
Select M3 and do a Data, Data Validation, List with a source of =INDIRECT($N$1)

So that gives you the selected Manufacturer, Product and Color in cells K3, L3 and M3.

That is so brilliant! It worked. I will spend some time to understand what you did and get back to you if I'm confused on what logic you used for the worker columns.

Thanks again for your expertise!

 

[Toadstool]

You're welcome!

Let me give the 5 cent tour...

I don't want to base my Manufacturer LoV on all of column C otherwise you'd offer a list of 6 Audi then 6 BMW.
To limit this my E3 formula says "Count how many of cell C3 are in cells C3 to C20" and of course the answer is 6, but the formula doesn't lock the starting row so when I copy down then in E4 it only looks in E4 to E20, so returns 5. This means the last entry for a Manufacturer will always have a count of 1 (cells E8 and E14).

Now my H3 formula says INDEX into the manufacturer with a row of the AGGREGATE which checks for any E$3:E$20=1.
The AGGREGATE is using the 15 option of SMALL so I can select the 1st, then the 2nd, 3rd, etc. match to that 1 value by specifying which I want. I do this with the H3 formula using ROWS(H$2:H2) part so ROWS says 1 for the first occurrence but as it's copied down that changes to ROWS(H$2:H3) which returns a 2 so I get the 2nd value of 1 which is in the BMW row.

Column F is concatenating Model and Manufacturer so I can avoid issues with Mazda Yaris and Toyota Yaris.

Column G is similar to column E but I'm counting down the Product only when the Manufacturer matches the one selected in K3 LoV.

The I3 and down formula is the same as H3 but looking for a 1 down column G.

The N3 and down is another INDEX and SMALL option of AGGREGATE but checking column F for rows where the concatenated Product and Manufacturer match those already selected from my K3 and L3 dynamic LoVs.


OK, well that was more like a 10 cents tour but I hope it helps.

 

[realdemigod]

Thanks for the clear explanation, and sorry for the late reply.

 

[realdemigod]

I'm unable to edit my post above, but the list of Audi could be anywhere in the particular row. With MATCH function I can get the first instance with the row number but I don't know how to use a row and MATCH together to give the contents in the cell for building the column E. Any idea how to do it?

 

[realdemigod]

I'm unable to edit my post above, but the list of Audi could be anywhere in the particular row. With MATCH function I can get the first instance with the row number but I don't know how to use a row and MATCH together to give the contents in the cell for building the column E. Any idea how to do it?

Is it possible to do with ADDRESS or CELL functions?

 

[realdemigod]

Hello Toadstool,

With ADDRESS and CELL functions together I can get the first reference of Audi but the reference comes like $C$3, but it won't help as I can't build the working column E.

=ADDRESS(MATCH("Audi",C1:C20,0),CELL("col",C1))

Could you tell me to get just $C3?

 

[Toadstool]

I'm not sure why you're trying to get an address in column E?

Let me post my proposed solution here again (after hopefully getting spaces around the chevrons as Fluff suggested).

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								$H$3:$H$4	$I$3:$I$4					$N$3:$N$6
2	Product	Colour	Manufacturer		ManU	ManProd	ProdU	Man	Prod		Manufacturer	Product	Color	Colors
3	Q7	Black	Audi		6	Q7Audi	4	Audi	Q7		Audi	Q7	Red	Black
4	X3	Black	BMW		6	X3BMW		BMW	Q5					Blue
5	Q7	Blue	Audi		5	Q7Audi	3							Red
6	Q5	Blue	Audi		4	Q5Audi	2							White
7	X3	Blue	BMW		5	X3BMW
8	X5	Blue	BMW		4	X5BMW
9	Q7	Red	Audi		3	Q7Audi	2
10	X5	Red	BMW		3	X5BMW
11	Q7	White	Audi		2	Q7Audi	1
12	Q5	White	Audi		1	Q5Audi	1
13	X3	White	BMW		2	X3BMW
14	X5	White	BMW		1	X5BMW
15					0

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H1 =ADDRESS(ROW(H3),COLUMN())&":"&ADDRESS(ROW(H3)+COUNTIF(H3:H43,"> ")-1,COLUMN()) I1 =ADDRESS(ROW(I3),COLUMN())&":"&ADDRESS(ROW(I3)+COUNTIF(I3:I43,"> ")-1,COLUMN()) N1 =ADDRESS(ROW(N3),COLUMN())&":"&ADDRESS(ROW(N3)+COUNTIF(N3:N43,"> ")-1,COLUMN()) E3 =COUNTIF($C3:$C$20,C3) F3 =A3&C3 G3 =IF(C3=K$3,COUNTIF($F3:$F$20,A3&C3),"") H3 =IF(COUNTIF(E:E,1) < ROWS(H$2:H2),"",INDEX(C$3:C$20,AGGREGATE(15,6,ROW(E$3:E$20)-ROW(H$2)/(E$3:E$20=1),ROWS(H$2:H2)))) I3 =IF(COUNTIF(G:G,1) < ROWS(I$2:I2),"",INDEX(A$3:A$20,AGGREGATE(15,6,ROW(F$3:F$20)-ROW(I$2)/(G$3:G$20=1),ROWS(I$2:I2)))) E4 =COUNTIF($C4:$C$20,C4) F4 =A4&C4 G4 =IF(C4=K$3,COUNTIF($F4:$F$20,A4&C4),"") H4 =IF(COUNTIF(E:E,1) < ROWS(H$2:H3),"",INDEX(C$3:C$20,AGGREGATE(15,6,ROW(E$3:E$20)-ROW(H$2)/(E$3:E$20=1),ROWS(H$2:H3)))) I4 =IF(COUNTIF(G:G,1) < ROWS(I$2:I3),"",INDEX(A$3:A$20,AGGREGATE(15,6,ROW(F$3:F$20)-ROW(I$2)/(G$3:G$20=1),ROWS(I$2:I3)))) <tbody> </tbody>

<tbody>
</tbody>

The only other change from the sheet I sent is I've sorted the colors just to prove that sequence doesn't change how it works. The E3 and copied down =COUNTIF($C3:$C$20,C3) will set a 1 next to the final entry for a manufacturer, regardless of the sequence.

I'm not sure what you're trying to achieve?