Data validation - dynamic and also dependent on a particular column
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 35

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

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

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

    @Toadstool
    When using < or > symbols you need to add a space either side, otherwise the board thinks that you are posting HTML code.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    215
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    You're welcome!

    Well we already have the list of possible colors in column N starting at row 3. You can put in N1
    =ADDRESS(ROW(N3),COLUMN())&":"&ADDRESS(ROW(N3)+COUNTIF(N3:N43,"> ")-1,COLUMN())
    so we'll get the address range of any colors with a non-blank entry.

    Now widen column M and in M2 we'll enter the heading "Select color".
    Select M3 and do a Data, Data Validation, List with a source of =INDIRECT($N$1)

    So that gives you the selected Manufacturer, Product and Color in cells K3, L3 and M3.

  3. #13
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    215
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    Quote Originally Posted by Fluff View Post
    @Toadstool
    When using < or > symbols you need to add a space either side, otherwise the board thinks that you are posting HTML code.
    Aaah! Thanks @Fluff. Let me check in this reply

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

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

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


    Yep! Looks good in Preview (well, as good as any formula that long can look).

  4. #14
    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
    You're welcome!

    Well we already have the list of possible colors in column N starting at row 3. You can put in N1
    =ADDRESS(ROW(N3),COLUMN())&":"&ADDRESS(ROW(N3)+COUNTIF(N3:N43,"> ")-1,COLUMN())
    so we'll get the address range of any colors with a non-blank entry.

    Now widen column M and in M2 we'll enter the heading "Select color".
    Select M3 and do a Data, Data Validation, List with a source of =INDIRECT($N$1)

    So that gives you the selected Manufacturer, Product and Color in cells K3, L3 and M3.
    That is so brilliant! It worked. I will spend some time to understand what you did and get back to you if I'm confused on what logic you used for the worker columns.

    Thanks again for your expertise!
    Last edited by realdemigod; Aug 23rd, 2019 at 10:22 AM.

  5. #15
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    215
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    You're welcome!

    Let me give the 5 cent tour...

    I don't want to base my Manufacturer LoV on all of column C otherwise you'd offer a list of 6 Audi then 6 BMW.
    To limit this my E3 formula says "Count how many of cell C3 are in cells C3 to C20" and of course the answer is 6, but the formula doesn't lock the starting row so when I copy down then in E4 it only looks in E4 to E20, so returns 5. This means the last entry for a Manufacturer will always have a count of 1 (cells E8 and E14).

    Now my H3 formula says INDEX into the manufacturer with a row of the AGGREGATE which checks for any E$3:E$20=1.
    The AGGREGATE is using the 15 option of SMALL so I can select the 1st, then the 2nd, 3rd, etc. match to that 1 value by specifying which I want. I do this with the H3 formula using ROWS(H$2:H2) part so ROWS says 1 for the first occurrence but as it's copied down that changes to ROWS(H$2:H3) which returns a 2 so I get the 2nd value of 1 which is in the BMW row.

    Column F is concatenating Model and Manufacturer so I can avoid issues with Mazda Yaris and Toyota Yaris.

    Column G is similar to column E but I'm counting down the Product only when the Manufacturer matches the one selected in K3 LoV.

    The I3 and down formula is the same as H3 but looking for a 1 down column G.

    The N3 and down is another INDEX and SMALL option of AGGREGATE but checking column F for rows where the concatenated Product and Manufacturer match those already selected from my K3 and L3 dynamic LoVs.


    OK, well that was more like a 10 cents tour but I hope it helps.

  6. #16
    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

    Thanks for the clear explanation, and sorry for the late reply.

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

    I'm unable to edit my post above, but the list of Audi could be anywhere in the particular row. With MATCH function I can get the first instance with the row number but I don't know how to use a row and MATCH together to give the contents in the cell for building the column E. Any idea how to do it?

  8. #18
    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 realdemigod View Post
    I'm unable to edit my post above, but the list of Audi could be anywhere in the particular row. With MATCH function I can get the first instance with the row number but I don't know how to use a row and MATCH together to give the contents in the cell for building the column E. Any idea how to do it?
    Is it possible to do with ADDRESS or CELL functions?

  9. #19
    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

    Hello Toadstool,

    With ADDRESS and CELL functions together I can get the first reference of Audi but the reference comes like $C$3, but it won't help as I can't build the working column E.

    =ADDRESS(MATCH("Audi",C1:C20,0),CELL("col",C1))

    Could you tell me to get just $C3?

  10. #20
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    215
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    I'm not sure why you're trying to get an address in column E?

    Let me post my proposed solution here again (after hopefully getting spaces around the chevrons as Fluff suggested).

    A B C D E F G H I J K L M N
    1 $H$3:$H$4 $I$3:$I$4 $N$3:$N$6
    2 Product Colour Manufacturer ManU ManProd ProdU Man Prod Manufacturer Product Color Colors
    3 Q7 Black Audi 6 Q7Audi 4 Audi Q7 Audi Q7 Red Black
    4 X3 Black BMW 6 X3BMW BMW Q5 Blue
    5 Q7 Blue Audi 5 Q7Audi 3 Red
    6 Q5 Blue Audi 4 Q5Audi 2 White
    7 X3 Blue BMW 5 X3BMW
    8 X5 Blue BMW 4 X5BMW
    9 Q7 Red Audi 3 Q7Audi 2
    10 X5 Red BMW 3 X5BMW
    11 Q7 White Audi 2 Q7Audi 1
    12 Q5 White Audi 1 Q5Audi 1
    13 X3 White BMW 2 X3BMW
    14 X5 White BMW 1 X5BMW
    15 0
    Sheet1

    Worksheet Formulas
    Cell Formula
    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())
    N1 =ADDRESS(ROW(N3),COLUMN())&":"&ADDRESS(ROW(N3)+COUNTIF(N3:N43,"> ")-1,COLUMN())
    E3 =COUNTIF($C3:$C$20,C3)
    F3 =A3&C3
    G3 =IF(C3=K$3,COUNTIF($F3:$F$20,A3&C3),"")
    H3 =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 =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))))
    E4 =COUNTIF($C4:$C$20,C4)
    F4 =A4&C4
    G4 =IF(C4=K$3,COUNTIF($F4:$F$20,A4&C4),"")
    H4 =IF(COUNTIF(E:E,1) < ROWS(H$2:H3),"",INDEX(C$3:C$20,AGGREGATE(15,6,ROW(E$3:E$20)-ROW(H$2)/(E$3:E$20=1),ROWS(H$2:H3))))
    I4 =IF(COUNTIF(G:G,1) < ROWS(I$2:I3),"",INDEX(A$3:A$20,AGGREGATE(15,6,ROW(F$3:F$20)-ROW(I$2)/(G$3:G$20=1),ROWS(I$2:I3))))


    The only other change from the sheet I sent is I've sorted the colors just to prove that sequence doesn't change how it works. The E3 and copied down =COUNTIF($C3:$C$20,C3) will set a 1 next to the final entry for a manufacturer, regardless of the sequence.

    I'm not sure what you're trying to achieve?

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
  •