Create a 2 column table that lists the variables and their corresponding
named range:

...........I..........J
1......CC.....SIX
2......NF.....SIX
3......AS.....SEVEN
4......PS......SEVEN
5......E........NINE
6......WN...NINE
etc
etc

As the source:

=INDIRECT(VLOOKUP(B5,I1:J6,2,0))

Biff

"Karen" wrote in message
...
Hi, I hope someone can help me. I'm using Excel 2002 I have the following
master formula in a data validation for one of my drop downs. The first
part
of my IF statement are defined names, which are formulas in itself. When I
try to accept the below validation, I get an error message that states:
You
may not use unions,intersections, or array constraints for Data Validation
criteria. Can I not use defined names. I don't have another choice because
if
try to include it all, I've reached the max characters????

Master Formula:
=IF((WARRANTOFF,PAYBANDONE,PAYBANDTHREE),(IF((OR(B 5="CC",B5="NF")),SIX,(IF((OR(B5="AS",B5="PS")),SEV EN,(IF((OR(B5="E",B5="WN")),NINE,(IF((B5="O"),TEN, (IF((B5="WD"),ELEVEN,(IF((OR(B5="GS",B5="NA",B5="N L",B5="WG",B5="WL")),FIFTEEN,NINETEEN))))))))))))) )

Named formulas
WARRANTOFF =IF(B5="W"),FOUR
PAYBANDONE =IF(B5="YP"),ONE
PAYBANDTHREE =IF((OR(B5="YA",B5="YB",B5="YC")))

Thank you!