Data validation lists shouldn't have to work that hard. Best to solve your problem on the worksheet itself, then have the validation list used the solved column of values
Here's an example of how I would do it. A helper column creates an indexable "key", the that key is parsed into columns for the validation lists, to use. You can change anything you want in columns A and B and the lists will reassert themselves.
Named ranges are used for rows 2-20 in the blue section, each column named for the color. For instance, highlight H2:H20 and you will see the name "Red" appear in the Name Box (to the left of the formula bar).
Anyway, once it's all working, you could hide all those helper columns, or even have them all a different (maybe hidden) worksheet.
Bookmarks