Data validation - dynamic and also dependent on a particular column
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 35

Thread: Data validation - dynamic and also dependent on a particular column

  1. #21
    New Member
    Join Date
    Aug 2014
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #22
    Board Regular
    Join Date
    Sep 2011
    Posts
    174
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #23
    New Member
    Join Date
    Aug 2014
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data validation - dynamic and also dependent on a particular column

    Quote Originally Posted by Tom.Jones View Post
    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. #24
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    219
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #25
    Board Regular
    Join Date
    Sep 2011
    Posts
    174
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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)
    Last edited by Tom.Jones; Aug 25th, 2019 at 06:45 PM.

  6. #26
    Board Regular
    Join Date
    Sep 2011
    Posts
    174
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

  7. #27
    New Member
    Join Date
    Aug 2014
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data validation - dynamic and also dependent on a particular column

    Quote Originally Posted by Toadstool View Post
    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. #28
    New Member
    Join Date
    Aug 2014
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data validation - dynamic and also dependent on a particular column

    Quote Originally Posted by Tom.Jones View Post
    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.
    Last edited by realdemigod; Aug 25th, 2019 at 09:41 PM.

  9. #29
    Board Regular
    Join Date
    Sep 2011
    Posts
    174
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data validation - dynamic and also dependent on a particular column

    Quote Originally Posted by realdemigod View Post
    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. #30
    New Member
    Join Date
    Aug 2014
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •