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

I'm having issues:

I have drop down boxes linking different sheets within the same workbook and they are dependent on each other.

Example:
When you select Option A in F4 (drop down box), subsequently the adjacent drop down 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 (drop down box), subsequently the adjacent drop down 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 (RedA, RedB, RedC) as well as Blue (BlueA, BlueB, BlueC) and Green (GreenA, GreenB, GreenC).
Option D in a F4 -> In F5: Red (RedA, RedB, RedC) as well as Blue (BlueA, BlueB, BlueC) and Green (GreenA, GreenB, GreenC).
Option E in a F4 -> In F5: Purple (PurpleA, PurpleB, PurpleC).
Option F in a F4 -> In F5: Red (RedA, RedB, RedC) as well as Purple (PurpleA, PurpleB, PurpleC).
Option G in a F4 -> In F5: Blue (BlueA, BlueB, BlueC) as well as Green (GreenA, GreenB, GreenC).
Option H in a F4 -> In F5: Red (RedA, RedB, RedC) as well as Blue (BlueA, BlueB, BlueC) and Green (GreenA, GreenB, GreenC).



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

This is how I do this.

1) All lists go on the first sheet, side by side in the format shown.
2) All named ranges are deleted
3) Name cell A1 on the Lists sheet as AnchorCell
4) Create a Dynamic Named range called OptionsList that is defined by this formula:
=OFFSET(AnchorCell, , , , COUNTA(Lists!$1:$1))
5) Then, on the Choices tab, the first selection window presents all options like so:
B4: Data Validation - Allow (list) - Source: =OptionsList

6) Then in B5, this cell's data validation formula does all the heavy lifting:
B5: Data Validation - Allow (list) - Source: =OFFSET(AnchorCell, 1, MATCH(B4, OptionsList, 0)-1, COUNTA(OFFSET(AnchorCell, , MATCH(B4, OptionsList, 0)-1, 1000, ))-1, 1)


This technique allows you to create as many dependent drop down lists as you want. Just list the titles in row1 and the options below, the DV formula will find them.

More examples here: DYNAMIC CASCADING DROP DOWNS THAT WORK, no INDIRECT PROBLEM
http://www.excelforum.com/excel-tips...t-problem.html