Data validation - dynamic and also dependent on a particular column

R

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
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.

[TABLE="width: 604"]
<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>[TR]
[TD="width: 77, bgcolor: transparent"] [/TD]
[TD="width: 77, bgcolor: transparent"] [/TD]
[TD="width: 99, bgcolor: transparent"] [/TD]
[TD="width: 26, bgcolor: transparent"] [/TD]
[TD="width: 26, bgcolor: transparent"] [/TD]
[TD="width: 26, bgcolor: transparent"] [/TD]
[TD="width: 26, bgcolor: transparent"] [/TD]
[TD="width: 77, bgcolor: transparent"]$H$3:$H$4[/TD]
[TD="width: 77, bgcolor: transparent"]$I$3:$I$4[/TD]
[TD="width: 31, bgcolor: transparent"] [/TD]
[TD="width: 96, bgcolor: transparent"] [/TD]
[TD="width: 77, bgcolor: transparent"] [/TD]
[TD="width: 92, bgcolor: transparent"]$N$3:$N$6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Product[/TD]
[TD="bgcolor: transparent"]Colour[/TD]
[TD="bgcolor: transparent"]Manufacturer[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Manufacturer[/TD]
[TD="bgcolor: transparent"]Product[/TD]
[TD="bgcolor: transparent"]Colors[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X3[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Red[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q5[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]White[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X3[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X5[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X5[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q5[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X3[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X5[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Tom.Jones said:
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.

[TABLE="width: 604"]
<tbody>[TR]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 99, bgcolor: transparent"][/TD]
[TD="width: 26, bgcolor: transparent"][/TD]
[TD="width: 26, bgcolor: transparent"][/TD]
[TD="width: 26, bgcolor: transparent"][/TD]
[TD="width: 26, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"]$H$3:$H$4[/TD]
[TD="width: 77, bgcolor: transparent"]$I$3:$I$4[/TD]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 96, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"]$N$3:$N$6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Product[/TD]
[TD="bgcolor: transparent"]Colour[/TD]
[TD="bgcolor: transparent"]Manufacturer[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Manufacturer[/TD]
[TD="bgcolor: transparent"]Product[/TD]
[TD="bgcolor: transparent"]Colors[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X3[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Red[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q5[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]White[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X3[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X5[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X5[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q7[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Q5[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent"]Audi[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X3[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]X5[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent"]BMW[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
If that Manufacturer data moves like this:

AAABAC
Manufacturer
Audi
BMW
Audi
Audi
BMW
BMW
Audi
BMW
Audi
Audi
BMW
BMW

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</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))))
 
Upvote 0
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?
Click to expand...


Yes.

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

Don't forget (CONTROL + SHIFT + ENTER)
 
Last edited:
Upvote 0
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.
Click to expand...

does not matter where Audi begins. Adjust your range.


Is it possible with your formula?
Click to expand...


Yes.

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

Don't forget (CONTROL + SHIFT + ENTER)
 
Upvote 0
Toadstool said:
If that Manufacturer data moves like this:

AAABAC
Manufacturer
Audi
BMW
Audi
Audi
BMW
BMW
Audi
BMW
Audi
Audi
BMW
BMW

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</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))))
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
257
drom
drom
M
  • Solved
Data Validation on Table Column with Multiple "Tags" Per Cell, Checked against List
Replies
8
Views
653
mc-lemons
M
O
  • Solved
Data Validation to only include certain date dependant rows from a dataset
Replies
3
Views
504
Peter_SSs
Peter_SSs
A
  • Question Question
Xlookup, Vlookup and/or VBA for finding, matching and inputting data to separate sheet
Replies
0
Views
825
AlfredDB
A
A
  • Question Question
Obtaining a Unique List of Values for Data Validation
Replies
1
Views
1K
a68tbird
A

Forum statistics

Threads
1,223,750
Messages
6,174,290
Members
452,554
Latest member
Louis1225

Share this page

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