Dynamic data validation in Excel

Question

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.

Answer 1

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.

Answer 2

@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!