# Dynamic data validation in Excel

On my first sheet I have all my options:

``````A        B
=============
yellow   DOG

Green    DOG

PINK     DOG

YELLOW   CAT

Pink     CAT

black    BIRD

RED      BIRD
``````

On my next sheet I want:

``````Animal     Choice of color
===========================================================
DOG        Data drop down box listing YELLOW Green and Pink

CAT        Data drop down box listing YELLOW and Pink

BIRD       Data drop down box listing black and RED
``````

This part is easy via data validation. If the data is static and never changes I can do a source range for each option and get it. The trouble is I don't know what order or how many animals or animal options there will be.

Is there a way to have my second sheet only show distinct values from sheet 1 column A and distinct values from sheet 1 column B where sheet2 Column A = sheet1 Column A?

So if I had sheet 1:

``````dog   red

cat   blue

dog   red

fish  purple

dog   yellow

cat   yellow

fish  green
``````

sheet 2 would be:

``````A     B
==============================
dog   drop down: red, yellow
cat   drop down: blue, yellow
fish  drop down: purple, green
``````

Note how the colors are only shown as options if it matches the animal it is for.

-

My solution was to create a third column that only displyed valid choices.

Sheet1

``````     A       B                C
1  yellow   DOG   =IF(Sheet2!\$B\$1=B1,A1,"")
2  Green    DOG   =IF(Sheet2!\$B\$1=B2,A2,"")
3  PINK     DOG   =IF(Sheet2!\$B\$1=B3,A3,"")
4  YELLOW   CAT   =IF(Sheet2!\$B\$1=B4,A4,"")
5  Pink     CAT   =IF(Sheet2!\$B\$1=B5,A5,"")
6  black    BIRD  =IF(Sheet2!\$B\$1=B6,A6,"")
7  RED      BIRD  =IF(Sheet2!\$B\$1=B7,A7,"")
``````

Sheet2

``````     A       B
1  Animal?  CAT
2  Colour?  ...
``````

You can replace `...` with a data validation list taken from `Sheet1!B1:B7`. It will contain empty options, but that's the closest I've come to what you're after.

If you want to make the formula neater, name cell `Sheet2!B1` as `Animal` and use that term in the formulas, like `=IF(Animal=B1,A1,"")`. It makes them much easier to read.

-

@Andy, please see below. I took your 2nd set of data (you have 2 different sets in the o.p.) which wasn't sorted and sorted it in a separate copy off to the side in columns G and H.

I next removed duplicates ("dog, red" is repeated).

Then I created the named range "AnimalsSorted", as shown in this figure:

Next, on Sheet2, I was able to create data validations for each of the cells in col B. The data validation source for each cell is a "list", with the formulas shown for clarity in column D:

Seems like this ought to work for you; good hunting!

-