Create a conditional drop-down list for entire column

[SerenityNetworks]

I can create a conditional drop-down list for a single cell. Example:

Constraining Column	Constrained Column
Red	Pink
Red	Orange
Blue	Green
Blue	Purple
Blue	Turquoise

Such that cell D1 shows a drop-down containing Red and Blue. Then if Red is selected the drop-down in E1 shows only Pink and Orange.
If Blue is selected in D1 then E1 only shows Green, Purple, and Turquoise.

No problem so far. This I can do.

But I need D2 to also show a drop-down containing Red and Blue, and E2 to be constrained by the D2 selection. Then I need D3 to also show a drop-down containing Red and Blue, and E3 to be constrained by the D3 selection - and so on for D4 & E4, D5 & E5, etc. Also, I need the data that contains the selections to be in a worksheet separate from the worksheet where the drop-downs appear.

I've tried using named ranges, but that's only good for D1 and E1. It fails for D2 & E2, and down. I've tried using formulas with the named ranges in the validation for the drop-down lists, but Excel doesn't like them I'm stumped.

Any help will be greatly appreciated.

 

[SerenityNetworks]

Well, I figured out how to do it within the same worksheet. I place the data in rows vs. columns. But since Data Validation doesn't let me reference cells in another worksheet (unless I use a named range), I'm stuck in the same worksheet. I can't create a named range for every row. So, the result works. It just isn't elegant.