Linking multiple drop down boxes with multiple ranges using data validation...Errors.

[cool1_boy1]

I'm having issues:

I have dropdownboxeslinking different sheets within the same workbook and they are dependent on each other.

Example:
When you select Option A in F4 (dropdown box), subsequently the adjacent dropdown box, F5, should list options under the name Red (RedA, RedB, RedC) as well as Blue (BlueA, BlueB, BlueC) and Green (GreenA, GreenB, GreenC).
When you select Option B in a F4 (dropdown box), subsequently the adjacent dropdown box, F5, should list options under the name Red (RedA, RedB, RedC) as well as Blue (BlueA, BlueB, BlueC) and Green (GreenA, GreenB, GreenC).
Option C in a F4 -> In F5: Red,Blue, and Green.
Option D in a F4 -> In F5: Red, Blue, and Green.
Option E in a F4 -> In F5: Purple.
Option F in a F4 -> In F5: Red, Purple.
Option G in a F4 -> In F5: Blue, Green.
Option H in a F4 -> In F5: Red,Blue, and Green.



..I get errors in excel saying that You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria.


Please see attached for the file that I'm referring to in the above example. (Color!F4 and Color!F5 are the drop down boxes that I'm referring to.)

To sum it all up:
My comments regarding the problems I'm having with excel:

This is what currently the data validation in F5 has:
IF(OR(COUNTIF($A$2:$A$6,$F$4),COUNTIF($A$10,$F$4)),Red,"")

This is what I want:
IF(OR(COUNTIF($A$2:$A$6,$F$4),COUNTIF($A$10,$F$4)),(Red, Blue, Green),"")

When I try to do what I want, this is the error I get:
You may not use reference operators…

How do I fix this? From what I want to achieve is it even possible?


Please help! I would prefer not using macros.

Thanks!!