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

 

[realdemigod]

My apologies for not being clear enough before. The Audi in the sheet I initially shared starts with row #3 but in the file I have to use, it could be anywhere on any row but the column is fixed. Depending on the output file it could change its position in terms of row placement but the column position is always the same.

So I'm trying to see how to use a row that I know and a value that I can extract to put it in the working column E and then calculate for the rest of the rows. For example if I know Audi is in the column AB (instead of C) and the first instance is row #400 , I want to start the working column E with =COUNTIF($AB400:AB3000, AB400). Same needs to be done for columns F and G.

So I'm looking for a way to use functions to give reference like $C3 not $C$3.

 

[Tom.Jones]

I haven't read all the posts and I don't know if you didn't want to use matrix formulas.
You can drop those helper columns and use in M3, a matrix formula (use Ctrl + **** + Enter).

=IFERROR(INDEX($B$3:$B$14,SMALL(IF(($C$3:$C$14=$K$3)*($A$3:$A$14=$L$3),ROW($B$3:$B$14)-ROW($B$3) + 1,"");ROWS($M$2:M2)));"")

Then pull the formula down to where it is needed.

$H$3:$H$4

$I$3:$I$4

$N$3:$N$6

Product

Colour

Manufacturer

Manufacturer

Product

Colors

Q7

Black

Audi

Audi

Q7

Black

X3

Black

BMW

Blue

Q7

Blue

Audi

Red

Q5

Blue

Audi

White

X3

Blue

BMW

X5

Blue

BMW

Q7

Red

Audi

X5

Red

BMW

Q7

White

Audi

Q5

White

Audi

X3

White

BMW

X5

White

BMW

<colgroup><col width="77" style="width: 58pt;" span="2"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 2885;"> <col width="26" style="width: 19pt; mso-width-source: userset; mso-width-alt: 744;" span="4"> <col width="77" style="width: 58pt;" span="2"> <col width="31" style="width: 23pt; mso-width-source: userset; mso-width-alt: 907;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 2792;"> <col width="77" style="width: 58pt;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 2676;"> <tbody> </tbody>

 

[realdemigod]

I haven't read all the posts and I don't know if you didn't want to use matrix formulas.
You can drop those helper columns and use in M3, a matrix formula (use Ctrl + **** + Enter).

=IFERROR(INDEX($B$3:$B$14,SMALL(IF(($C$3:$C$14=$K$3)*($A$3:$A$14=$L$3),ROW($B$3:$B$14)-ROW($B$3) + 1,"");ROWS($M$2:M2)));"")

Then pull the formula down to where it is needed.

$H$3:$H$4

$I$3:$I$4

$N$3:$N$6

Product

Colour

Manufacturer

Manufacturer

Product

Colors

Q7

Black

Audi

Audi

Q7

Black

X3

Black

BMW

Blue

Q7

Blue

Audi

Red

Q5

Blue

Audi

White

X3

Blue

BMW

X5

Blue

BMW

Q7

Red

Audi

X5

Red

BMW

Q7

White

Audi

Q5

White

Audi

X3

White

BMW

X5

White

BMW

<tbody>
</tbody>

Thanks for your help but I don't know where Audi begins on the worksheet and also I need to let a user choose Audi and then the product and then the colour. Is it possible with your formula?

 

[Toadstool]

If that Manufacturer data moves like this:

	AA	AB	AC
399
400		Manufacturer
401		Audi
402		BMW
403		Audi
404		Audi
405		BMW
406		BMW
407		Audi
408		BMW
409		Audi
410		Audi
411		BMW
412		BMW
413

<tbody>
</tbody>

Sheet1​

...and can continue down to AB3600 then the formula in column E becomes =COUNTIF($AB401:$AB$3600,AB401)

Don't forget all those columns with formulae must also be copied down 3,200 rows and the Product and Color must also have 3,200 rows of data.

F3 becomes =A3&AB401
G3 becomes =IF(COUNTIF(G:G,1) < ROWS(I$2:I2),"",INDEX(A$3:A$3203,AGGREGATE(15,6,ROW(F$3:F$3203)-ROW(I$2)/(G$3:G$3203=1),ROWS(I$2:I2))))

 

[Tom.Jones]

Thanks for your help but I don't know where Audi begins on the worksheet and also I need to let a user choose Audi and then the product and then the colour. Is it possible with your formula?

Yes.

Choose MANUFACTURER then choose PRODUCT and you will get COLOR for what you choose.

Don't forget (CONTROL + SHIFT + ENTER)

 

[Tom.Jones]

Thanks for your help but I don't know where Audi begins on the worksheet and also I need to let a user choose Audi and then the product and then the colour.

does not matter where Audi begins. Adjust your range.

Is it possible with your formula?

Yes.

Choose MANUFACTURER then choose PRODUCT and you will get COLOR for what you choose.

Don't forget (CONTROL + SHIFT + ENTER)

 

[realdemigod]

If that Manufacturer data moves like this:

	AA	AB	AC
399
400		Manufacturer
401		Audi
402		BMW
403		Audi
404		Audi
405		BMW
406		BMW
407		Audi
408		BMW
409		Audi
410		Audi
411		BMW
412		BMW
413

<tbody>
</tbody>

Sheet1​

...and can continue down to AB3600 then the formula in column E becomes =COUNTIF($AB401:$AB$3600,AB401)

Don't forget all those columns with formulae must also be copied down 3,200 rows and the Product and Color must also have 3,200 rows of data.

F3 becomes =A3&AB401
G3 becomes =IF(COUNTIF(G:G,1) < ROWS(I$2:I2),"",INDEX(A$3:A$3203,AGGREGATE(15,6,ROW(F$3:F$3203)-ROW(I$2)/(G$3:G$3203=1),ROWS(I$2:I2))))

Thanks I got that but I can't put the formula manually every time after looking at the worksheet where Audi is starting that is why I thought I could use MATCH function to get the first instance and then use the column AB and the row number returned by MATCH function.

But I don't know how to use a column and a number to use as a reference. I tried different things but not able to achieve.

 

[realdemigod]

does not matter where Audi begins. Adjust your range.

Thanks, I don't know the range and I can know the first instance where Audi begins.

 

[Tom.Jones]

Thanks, I don't know the range and I can know the first instance where Audi begins.

I don't understand.

Upload a file with data (in a file sharing site) and I will show you.

 

[realdemigod]

Here is the file. You can ignore the columns after the third one (manufacturer). Thanks