# Data validation list of subset

1. ## Data validation list of subset

Hi,

I have made an example workbook
example workbook.xlsx

In cell D4 I would like to make a data validation list (dropdown) that would only show the animals with the color specified in the data validation list in cell C4 (based on the table below).

It also needs to be noted that the number of animals/entries are "unknown", i.e. I would like to use cell B4 as an offset (see data validation list formula for D4).

Thankful for any help.

2. ## Re: Data validation list of subset

For D4 use the Source of

=OFFSET(\$B\$8,0,1,\$B\$4)

3. ## Re: Data validation list of subset

I only want the data validation list of D4 to display the animals with the corresponding color chosen in the list in C4.

For example:
If i choose "Red" in C4
i want the D4 list to show
Fish
Shark
Tiger
Hippo
Snake
Scorpion

I do _not_ just want a list of all animals in the D column in the D4 data validation list.

4. ## Re: Data validation list of subset

Here's one way. In E8 copied down, is this Arrayed Formula

=IFERROR(INDEX(\$C\$8:\$C\$20, SMALL(IF(\$B\$8:\$B\$20=\$C\$4, ROW(\$B\$8:\$B\$20)-ROW(\$B\$8)+1),ROWS(\$A\$1:\$A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I then defined a dynamic range called SubList

Then just used simple DataValdiation >Use List > =SubList
You can hide Column E if you like

5. ## Re: Data validation list of subset

Thanks ChemistB, that way I already knew of, i was thinking if there was a smart way of getting the subset directly in the data validation formula, but it doesn't seem to be

6. ## Re: Data validation list of subset

I believe the list must be a range of congruent cells.

7. ## Re: Data validation list of subset

worksheet_Change event can be used, which does not require Helper column
Code
``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1