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]

Hi Demigod,

I was all ready to restructure your data so I could benefit from the use of Tables and Names but then I read "the placements of the columns are fixed" so I've resorted to worker columns. Once you've copied the formulae down the rows as far as the maximum you'll ever need then of course you can hide worker columns E to I.

I didn't know what codes you may use for other Products and I was sure you'd not want to confuse a Mazda Yaris with a Toyota Yaris so it got complex.

Columns A to C are your data
Columns E to I are my worker columns, including some INDIRECT and ADDRESS functions so the LoV is limited to those which are valid (i.e. no blanks in the list). Lots of INDEX, AGGREGATE and COUNTIF functions to limit how many rows are displayed and to retrieve the data.
Columns K to N are where you select the Manufacturer and Product dropdowns to see the list of available colours.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
$H$3:$H$6	$I$3:$I$4
Product	Colour	Manufacturer	Man	Prod	Manufacturer	Product	Colours
Q7	White	Audi	Audi	Q7	Audi	Q5	Blue
Q7	Blue	Audi	BMW	Q5	White
Q7	Red	Audi	Mazda		Grey
Q7	Black	Audi	Toyota		Yellow
Q5	Blue	Audi
Q5	White	Audi

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: center"]ManU[/TD]
[TD="align: center"]ManProd[/TD]
[TD="align: center"]ProdU[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]Q7Audi[/TD]
[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]Q7Audi[/TD]
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Q7Audi[/TD]
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Q7Audi[/TD]
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Q5Audi[/TD]
[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Q5Audi[/TD]
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=ADDRESS(ROW(H3),COLUMN())&":"&ADDRESS(ROW(H3)+COUNTIF(H3:H43,"> ")-1,COLUMN())[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I1[/TH]
[TD="align: left"]=ADDRESS(ROW(I3),COLUMN())&":"&ADDRESS(ROW(I3)+COUNTIF(I3:I43,"> ")-1,COLUMN())[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=COUNTIF($C3:$C$20,C3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=A3&C3[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=IF(C3=K$3,COUNTIF($F3:$F$20,A3&C3),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=IF(<font color="Blue">COUNTIF(E:E,1)<rows(<font color="Red">H$2:H2C$3:C$20,AGGREGATE(15,6,ROW(E$3:E$20)-ROW(H$2)/(E$3:E$20=1),ROWS(H$2:H2))</rows())[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=IF(<font color="Blue">COUNTIF(G:G,1)<rows(<font color="Red">I$2:I2A$3:A$20,AGGREGATE(15,6,ROW(F$3:F$20)-ROW(I$2)/(G$3:G$20=1),ROWS(I$2:I2))</rows())[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N3[/TH]
[TD="align: left"]=IF(<font color="Blue">COUNTIF($F$3:$F$20,L$3&K$3)<rows(<font color="Red">N$2:N2B$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))</rows())[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[realdemigod]

Hello Toadstool,
Thank you very much for your help. Wish there was a way to upload xl files to make it easier rather than you create tables. I will try this and get back to you.

 

[realdemigod]

Hello Toadstool,
The formulas H3 to N3 are giving error, I'm not able to figure out what is wrong.

 

[Akuini]

Wish there was a way to upload xl files to make it easier rather than you create tables.

You could upload a sample workbook to a site such dropbox.com then put the link here.

 

[realdemigod]

You could upload a sample workbook to a site such dropbox.com then put the link here.

Hello Akuini, thanks. I didn't know if it was allowed. Here is the file.

Referring to the file K3, should let a user to choose from Audi or BMW, then the product at L3 and then colour at M3.

Thanks for your help.

 

[Toadstool]

Realdemigod,

You're missing some formulae for H, I and N columns.

[TABLE="width: 600"]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Audi[/TD]
[TD]Q7[/TD]
[TD="align: right"][/TD]
[TD]Audi[/TD]
[TD]Q5[/TD]
[TD="align: right"][/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]BMW[/TD]
[TD]Q5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Mazda[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Grey[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]Toyota[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Yellow[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3
to H20
[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3
to I20
[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N3 to N20
[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Now copy down to row 20 all formulae in E3, F3, G3, H3 and I3.

The key part I missed for the LoV was:
In K3 Data Validation, List, source is =indirect($H$1)
In L3 Data Validation, List, source is =indirect($I$1)

 

[realdemigod]

Hello Toadstool, I'm unable to enter the formulas in H3, I3 and N3. I have uploaded the error to the link below.

[FONT=&quot]https://imgur.com/R6cL1X4[/FONT]

 

[Toadstool]

I was using the "MrExcel HTML Maker" and there seems to be a challenge with it interpreting those formulae.

Let me try a different way.... Nope! Always get corrupted formulae.

OK, Let me give you a dropbox version (first time I've use Dropbox to send) which is the version you shared with the formulae I listed above, including the Data Validation LoVs.


https://www.dropbox.com/s/stkpx84yl21xo67/MrExcel-Realdemigod-v03.xlsx?dl=0

 

[realdemigod]

Thanks a lot Toadstool! It works. Thank you for taking the pain to upload the file to dropbox!

I may take a while to understand how you did it. Instead of all the colours shown at once I need to let a user to choose the colours as well depending on the product. Is it possible?