Data validation - dynamic and also dependent on a particular column
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

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

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

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

    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 Code:
    https://imgur.com/aevnBpg

    Thanks

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

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

    Hi Demigod,

    I was all ready to restructure your data so I could benefit from the use of Tables and Names but then I read "the placements of the columns are fixed" so I've resorted to worker columns. Once you've copied the formulae down the rows as far as the maximum you'll ever need then of course you can hide worker columns E to I.

    I didn't know what codes you may use for other Products and I was sure you'd not want to confuse a Mazda Yaris with a Toyota Yaris so it got complex.

    Columns A to C are your data
    Columns E to I are my worker columns, including some INDIRECT and ADDRESS functions so the LoV is limited to those which are valid (i.e. no blanks in the list). Lots of INDEX, AGGREGATE and COUNTIF functions to limit how many rows are displayed and to retrieve the data.
    Columns K to N are where you select the Manufacturer and Product dropdowns to see the list of available colours.

    A B C D E F G H I J K L M N
    1 $H$3:$H$6 $I$3:$I$4
    2 Product Colour Manufacturer ManU ManProd ProdU Man Prod Manufacturer Product Colours
    3 Q7 White Audi 8 Q7Audi 4 Audi Q7 Audi Q5 Blue
    4 Q7 Blue Audi 7 Q7Audi 3 BMW Q5 White
    5 Q7 Red Audi 6 Q7Audi 2 Mazda Grey
    6 Q7 Black Audi 5 Q7Audi 1 Toyota Yellow
    7 Q5 Blue Audi 4 Q5Audi 4
    8 Q5 White Audi 3 Q5Audi 3
    Sheet2

    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())
    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)H$2:H2C$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)I$2:I2A$3:A$20,AGGREGATE(15,6,ROW(F$3:F$20)-ROW(I$2)/(G$3:G$20=1),ROWS(I$2:I2))
    N3 =IF(COUNTIF($F$3:$F$20,L$3&K$3)N$2:N2B$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))

  3. #3
    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,
    Thank you very much for your help. Wish there was a way to upload xl files to make it easier rather than you create tables. I will try this and get back to you.

  4. #4
    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,
    The formulas H3 to N3 are giving error, I'm not able to figure out what is wrong.

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,949
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

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

    Quote Originally Posted by realdemigod View Post
    Wish there was a way to upload xl files to make it easier rather than you create tables.
    You could upload a sample workbook to a site such dropbox.com then put the link here.

  6. #6
    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 Akuini View Post
    You could upload a sample workbook to a site such dropbox.com then put the link here.
    Hello Akuini, thanks. I didn't know if it was allowed. Here is the file.

    Referring to the file K3, should let a user to choose from Audi or BMW, then the product at L3 and then colour at M3.

    Thanks for your help.

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

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

    Realdemigod,

    You're missing some formulae for H, I and N columns.

    H I J K L M N
    3 Audi Q7 Audi Q5 Blue
    4 BMW Q5 White
    5 Mazda Grey
    6 Toyota Yellow
    Sheet2

    Worksheet Formulas
    Cell Formula
    H3
    to H20
    =IF(COUNTIF(E:E,1)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
    to I20
    =IF(COUNTIF(G:G,1)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))))
    N3 to N20 =IF(COUNTIF($F$3:$F$20,L$3&K$3)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))))

    Now copy down to row 20 all formulae in E3, F3, G3, H3 and I3.

    The key part I missed for the LoV was:
    In K3 Data Validation, List, source is =indirect($H$1)
    In L3 Data Validation, List, source is =indirect($I$1)
    Last edited by Toadstool; Aug 23rd, 2019 at 07:04 AM.

  8. #8
    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, I'm unable to enter the formulas in H3, I3 and N3. I have uploaded the error to the link below.

    Code:
    https://imgur.com/R6cL1X4

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

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

    I was using the "MrExcel HTML Maker" and there seems to be a challenge with it interpreting those formulae.

    Let me try a different way.... Nope! Always get corrupted formulae.

    OK, Let me give you a dropbox version (first time I've use Dropbox to send) which is the version you shared with the formulae I listed above, including the Data Validation LoVs.


    https://www.dropbox.com/s/stkpx84yl2...-v03.xlsx?dl=0

  10. #10
    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 a lot Toadstool! It works. Thank you for taking the pain to upload the file to dropbox!

    I may take a while to understand how you did it. Instead of all the colours shown at once I need to let a user to choose the colours as well depending on the product. Is it possible?
    Last edited by realdemigod; Aug 23rd, 2019 at 09:45 AM.

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
  •