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!

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!

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!

"T. Valko" wrote:

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!

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.

"Karen" wrote:

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!

"T. Valko" wrote:

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!

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

Biff

"Karen" wrote in message
...
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.

"Karen" wrote:

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!

"T. Valko" wrote:

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!