# Thread: Data validation - dynamic and also dependent on a particular column Thanks: 0 Likes:  7 Post #5330442 (1)Post #5330659 (1)Post #5330513 (1)Post #5331447 (1)Post #5330589 (1)

1. ## Re: Data validation - dynamic and also dependent on a particular column

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.

2. ## Re: Data validation - dynamic and also dependent on a particular column

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

3. ## Re: Data validation - dynamic and also dependent on a particular column

Originally Posted by 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
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?

4. ## Re: Data validation - dynamic and also dependent on a particular column

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
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))))

5. ## Re: Data validation - dynamic and also dependent on a particular column

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)

6. ## Re: Data validation - dynamic and also dependent on a particular column

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)

7. ## Re: Data validation - dynamic and also dependent on a particular column

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
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.

8. ## Re: Data validation - dynamic and also dependent on a particular column

Originally Posted by Tom.Jones
Thanks, I don't know the range and I can know the first instance where Audi begins.

9. ## Re: Data validation - dynamic and also dependent on a particular column

Originally Posted by realdemigod
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.

10. ## Re: Data validation - dynamic and also dependent on a particular column

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