Data validation - dynamic and also dependent on a particular column

realdemigod

Board Regular
Joined
Aug 19, 2014
Messages
51
Office Version
  1. 365
Platform
  1. MacOS
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?

HTML:
https://imgur.com/aevnBpg

Thanks
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.

ABCDEFGHIJKLMN
1$H$3:$H$6$I$3:$I$4
2ProductColourManufacturerManUManProdProdUManProdManufacturerProductColours
3Q7WhiteAudi8Q7Audi4AudiQ7AudiQ5Blue
4Q7BlueAudi7Q7Audi3BMWQ5White
5Q7RedAudi6Q7Audi2MazdaGrey
6Q7BlackAudi5Q7Audi1ToyotaYellow
7Q5BlueAudi4Q5Audi4
8Q5WhiteAudi3Q5Audi3

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
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())
E3=COUNTIF($C3:$C$20,C3)
F3=A3&C3
G3=IF(C3=K$3,COUNTIF($F3:$F$20,A3&C3),"")
H3=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())
I3=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())
N3=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())

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
Hello Toadstool,
The formulas H3 to N3 are giving error, I'm not able to figure out what is wrong.
 
Upvote 0
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.
 
Upvote 0
Realdemigod,

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

HIJKLMN
3AudiQ7AudiQ5Blue
4BMWQ5White
5MazdaGrey
6
ToyotaYellow

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
H3
to H20
=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
to I20
=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))))
N3 to N20
=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))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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)
 
Last edited:
Upvote 0
Hello Toadstool, I'm unable to enter the formulas in H3, I3 and N3. I have uploaded the error to the link below.

Code:
[FONT=&quot]https://imgur.com/R6cL1X4[/FONT]
 
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top