Data validation - dynamic and also dependent on a particular column

realdemigod

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

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
821
Office Version
2016
Platform
Windows
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>
 

realdemigod

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

Board Regular
Joined
Aug 19, 2014
Messages
51
Office Version
365
Platform
MacOS
Hello Toadstool,
The formulas H3 to N3 are giving error, I'm not able to figure out what is wrong.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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

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

Well-known Member
Joined
Mar 5, 2018
Messages
821
Office Version
2016
Platform
Windows
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:

realdemigod

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
821
Office Version
2016
Platform
Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,102,633
Messages
5,488,010
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top