Sources Used in Data Validation

[Karen]

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(B5="CC",B5="NF")),SIX,(IF((OR(B5="AS",B5="PS")),SEVEN,(IF((OR(B5="E",B5="WN")),NINE,(IF((B5="O"),TEN,(IF((B5="WD"),ELEVEN,(IF((OR(B5="GS",B5="NA",B5="NL",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!

 

[T. Valko]

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]

This works wonderfully! Now, how do I get the validation to continue in the
same column? I applied it to, for the below example, B5. I tried to copy and
paste special the validation down the same column from B6-B100, but it
doesn't take. I tried to enter $B$5, but that didn't work. How can apply
this validation without having to go to each cell and create the same
validation? Thanks!

 

[Karen]

I've figured it out! I needed to make my table array an absolute reference,
so that the lookup is always looking in that range no matter what row I'm on.
I copied and used paste special validation and it works perfectly.

 

[T. Valko]

Glad to hear you got it figured out! Thanks for the feedback.

Biff