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.