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

Corran · Aug 24th 2016

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!

**AliGW** · Aug 24th 2016

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

Corran · Aug 24th 2016

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

Quote from AliGW;776181

What you are describing ARE dependent lists.

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

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.

**AliGW** · Aug 24th 2016

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.

Corran · Aug 24th 2016

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/

**AliGW** · Aug 24th 2016

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.

**mikerickson** · Aug 24th 2016

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.

Green Crocodile · Aug 28th 2016

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

Corran · Aug 29th 2016

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!

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

Participate now!

Share