|
Sources Used in Data Validation
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! |
Sources Used in Data Validation
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! |
Sources Used in Data Validation
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! |
Sources Used in Data Validation
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! |
Sources Used in Data Validation
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! |
| All times are GMT +1. The time now is 11:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com