List Validation: displaying only those values from col A that have B set to value n

  • It turns out this is suprisingly hard to Google.


    What I have is a large sheet with basically in column A a name, in column B a category and then in many other columns more information.


    What I would like is to be able is to select a category type from a data validation list dropdown and then the second data validation list should only contain names from column A that have that category in column B. Creating the first dropdown is easy and can be done by hand but it seems there is no way to do the second part.


    Googling only gives results for dependent lists which is not what I want.


    A super simple, made up, example of what I'm talking about:


    Code
    Name              Band                   Instrument
    John Lennon     The Beatles          Guitar
    Paul McCartney The Beatles          Bass Guitar
    Mick Jagger      The Rolling Stones Harmonica
    Etc.


    So from the first dropdown I would select The Beatles and then only get to see (and select) George, John, Paul, and Ringo from the second dropdown.


    Is this possible at all or does Excel have no way of supporting this?


    Thanks!

  • Re: List Validation: displaying only those values from col A that have B set to value


    What you are describing ARE dependent lists. ;)


    http://www.contextures.com/xlDataVal02.html

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: List Validation: displaying only those values from col A that have B set to value


    Quote from Corran;776185

    I'm sorry, but it really isn't. I think you might need to read my problem again.


    I've read the page you linked and at least ten like it and none do what I want.


    I have read your request and it is effectively describing dependent drop-down lists. I think you might need to explain the problem again: what exactly do you want to see? Mock something up in Excel and post the workbook here.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: List Validation: displaying only those values from col A that have B set to value


    Quote from AliGW;776188

    I have read your request and it is effectively describing dependent drop-down lists. I think you might need to explain the problem again: what exactly do you want to see? Mock something up in Excel and post the workbook here.


    Here's an example, basically what I explained above.


    forum.ozgrid.com/index.php?attachment/70032/

  • Re: List Validation: displaying only those values from col A that have B set to value


    Yes, thanks. There is no shortcut: you need to create a list somewhere (could be on a hidden sheet) that will feed the drop-down list.


    This array formula will check the band and allow you to list its members:


    =IFERROR(INDEX(Blad1!A:A,SMALL(IF(Blad1!$B$2:$B$4=Blad2!$B$1,ROW(Blad1!$B$2:$B$4)),ROWS($1:1))),"")


    Confirm with CTRL+SHIFT+ENTER (not just enter) and drag down. You will need to adjust the B range to suit the length of your entire table on sheet 1.


    You can create a named range for your list like this:


    =OFFSET(Blad2!$D$2,0,0,COUNTA(Blad2!$D:$D)-1)


    and this will ensure that your list always includes everything, even if the source list grows. This is assuming that your array list starts in D2 on sheet 2, but it can be anywhere.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: List Validation: displaying only those values from col A that have B set to value


    Since you are creating this extra list in a discrete place, you can use helper columns to avoid the performance hit from using CSE formulas.


    For example, if you create a new sheet (Blad3), you can put
    =IF(Blad1!B2=Blad2!$B$1,ROW(Blad1!B2), 99999) in Blad3!$A1
    =SMALL($A:$A, ROWS($$1:1)) in Blad3$B$1
    =INDEX(Blad1!$A:$A, B1, 1) in Blad3!$C$1


    then the name for your list would be
    Name: Musicians RefersTo: =Blad3!$C$1:INDEX(Blad3!$C:$C, COUNTIF(Blad3!$A:$A, "<99999"), 1)


    and your list source would be =Musicians


    If you are adding a hidden sheet, you might as well use the cells to make other helper columns that give you a dynamic list of Bands that adapts as you add new bands to the list and also sorts them by name.


    Hidden working sheets can make for some very slick workbooks.

  • Re: List Validation: displaying only those values from col A that have B set to value


    Is this what you wanted? No array and no helpers. Will only work if the bands are grouped together (all beatles together, all rolling stones together, etc). See attached.


    In Data Validation put: =OFFSET(Blad1!$A$1,MATCH(Blad2!$B$1,Blad1!$B:$B,0)-1,,COUNTIF(Blad1!$B:$B,Blad2!$B$1))

  • Re: List Validation: displaying only those values from col A that have B set to value


    I'd like to thank everyone for their answers to my question, I didn't have the proper time to look at them before now.


    It sure is a shame that Excel has no better built in solutions for this problem. However, it was nice to see people have come up with quite a few different solutions to this.


    In the end I went with Green Crocodile's solution for one of the lists/sheets I have. It's a shame that this means I can't also have an alphabetical list for the entire column (when not filtering on anything) but this is a price worth paying in this particular case.


    The other list/sheet has 1300 entries and most are all of the same category so I'm still looking at what the best solution is for that one.


    Thanks everyone!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!