Good morning all. I have been wracking my brain trying to get the proper syntax for a formula that will take the information from 3 different cells providing multiple scenarios and give a specific envelope size result based that criteria. The criteria is as follows (table sample below):
=IF(B13="","",IF(AND(B6="01 CAS: CAHPS",B6="02 CCS: CAHPS",B6="03 CCS CCC: CAHPS",B6="04 MAS: CAHPS",B6="05 MCS: CAHPS",B6="06 MCS CCC: CAHPS",B6="06 MCS CCC: CAHPS Simulation",B6="07 HCAHPS",B6="08 HOS Simulation",B6="09 Call Sat",B6="09 Case Management",B6="09 CG CAHPS",B6="09 Disease Management",B6="09 ECHO - Behavioral Health",B6="09 Member Sat",B6="09 OAS CAHPS",B6="09 Provider Sat",B6="09 CAHPS Simulation",B6="50 PCMH - Adult",B6="60 PCMH - Child",I13="Single Stuff",I13="Split",j6="1 - 8.5 x 11", j6="2 - 8.5 x 14",j6="1 - 11 x 17", j6="2 - 11 x 17"),"#10.5 Window"),
IF(AND(B6="01 CAS: CAHPS",B6="02 CCS: CAHPS",B6="03 CCS CCC: CAHPS",B6="04 MAS: CAHPS",B6="05 MCS: CAHPS",B6="06 MCS CCC: CAHPS",B6="06 MCS CCC: CAHPS Simulation",B6="07 HCAHPS",B6="08 HOS Simulation",B6="09 Call Sat",B6="09 Case Management",B6="09 CG CAHPS",B6="09 Disease Management",B6="09 ECHO - Behavioral Health",B6="09 Member Sat",B6="09 OAS CAHPS",B6="09 Provider Sat",B6="09 CAHPS Simulation",B6="50 PCMH - Adult",B6="60 PCMH - Child",I13="Double Stuff",j6="1 - 11 x 17", j6="2 - 11 x 17"),"6 x 10 Window"),
IF(AND(B6="09 MCAHPS Simulation",B6="09 QHP Simulation",B6="10 MA ONLY: MCAHPS",B6="20 PDP ONLY: MCAHPS",B6="30 MA-PDP: MCAHPS",B6="70 ACO CAHPS",B6="80 QHP",B6="90 MIPS CAHPS",I20="Single Stuff",I13="Split",j6="1 - 11 x 17", j6="2 - 11 x 17",j6="3 - 11 x 17"),"6 x 10 Window"),
IF(AND(B6="70 ACO CAHPS",B6="80 QHP",B6="09 QHP Simulation"B6="90 MIPS CAHPS",I13="Double Stuff",j6="1 - 11 x 17", j6="2 - 11 x 17",j6="3 - 11 x 17"),"6 x 10 Window"),
IF(AND(B6="09 MCAHPS Simulation",B6="10 MA ONLY: MCAHPS",B6="20 PDP ONLY: MCAHPS",B6="30 MA-PDP: MCAHPS",I13="Double Stuff",j6="3 - 11 x 17"),"9 x 12 Window")
<tbody>
</tbody>
Please excuse my long hand. I know this is ridiculously extra and not the easiest way to accomplish this, but since it is not a range of cells indexing or lookup functions wouldn’t work, I keep getting too many function errors on any If/And functions, and I don’t know VBA to try and make it work that way. I just need to find a way to make sure the correct envelope size is being chosen automatically per project specs so that I can avoid having to request reprints for account people who can’t seem to figure this out. Please help!
Thank you in advance for any assistance rendered!
=IF(B13="","",IF(AND(B6="01 CAS: CAHPS",B6="02 CCS: CAHPS",B6="03 CCS CCC: CAHPS",B6="04 MAS: CAHPS",B6="05 MCS: CAHPS",B6="06 MCS CCC: CAHPS",B6="06 MCS CCC: CAHPS Simulation",B6="07 HCAHPS",B6="08 HOS Simulation",B6="09 Call Sat",B6="09 Case Management",B6="09 CG CAHPS",B6="09 Disease Management",B6="09 ECHO - Behavioral Health",B6="09 Member Sat",B6="09 OAS CAHPS",B6="09 Provider Sat",B6="09 CAHPS Simulation",B6="50 PCMH - Adult",B6="60 PCMH - Child",I13="Single Stuff",I13="Split",j6="1 - 8.5 x 11", j6="2 - 8.5 x 14",j6="1 - 11 x 17", j6="2 - 11 x 17"),"#10.5 Window"),
IF(AND(B6="01 CAS: CAHPS",B6="02 CCS: CAHPS",B6="03 CCS CCC: CAHPS",B6="04 MAS: CAHPS",B6="05 MCS: CAHPS",B6="06 MCS CCC: CAHPS",B6="06 MCS CCC: CAHPS Simulation",B6="07 HCAHPS",B6="08 HOS Simulation",B6="09 Call Sat",B6="09 Case Management",B6="09 CG CAHPS",B6="09 Disease Management",B6="09 ECHO - Behavioral Health",B6="09 Member Sat",B6="09 OAS CAHPS",B6="09 Provider Sat",B6="09 CAHPS Simulation",B6="50 PCMH - Adult",B6="60 PCMH - Child",I13="Double Stuff",j6="1 - 11 x 17", j6="2 - 11 x 17"),"6 x 10 Window"),
IF(AND(B6="09 MCAHPS Simulation",B6="09 QHP Simulation",B6="10 MA ONLY: MCAHPS",B6="20 PDP ONLY: MCAHPS",B6="30 MA-PDP: MCAHPS",B6="70 ACO CAHPS",B6="80 QHP",B6="90 MIPS CAHPS",I20="Single Stuff",I13="Split",j6="1 - 11 x 17", j6="2 - 11 x 17",j6="3 - 11 x 17"),"6 x 10 Window"),
IF(AND(B6="70 ACO CAHPS",B6="80 QHP",B6="09 QHP Simulation"B6="90 MIPS CAHPS",I13="Double Stuff",j6="1 - 11 x 17", j6="2 - 11 x 17",j6="3 - 11 x 17"),"6 x 10 Window"),
IF(AND(B6="09 MCAHPS Simulation",B6="10 MA ONLY: MCAHPS",B6="20 PDP ONLY: MCAHPS",B6="30 MA-PDP: MCAHPS",I13="Double Stuff",j6="3 - 11 x 17"),"9 x 12 Window")
| A | B | C | D | E | F | G | H | I | J | |
| 1 | ||||||||||
| 2 | PROJECT DESCRIPTION | |||||||||
| 3 | MPL #: | 101020 | * Account Project Manager: | |||||||
| 4 | * Client Name: | ABC Company | * Project Coordinator: | |||||||
| 5 | * Parent Company: | Mom & Pop Company | * Prior Sample #: | 9128000 | ||||||
| 6 | * Project Description: | 01 CAS: CAHPS | * Survey Size: | 1 – 11 x 17 | ||||||
| 7 | * Total Seeds: | 1 | ||||||||
| 8 | * # Waves with Project: | 2 | ||||||||
| 9 | * Enter language if “Other” is selected: | English | ||||||||
| 10 | *Initial Scheduled Mail Date: | TBD | ||||||||
| 11 | ||||||||||
| 12 | Sample Number(s) | Sample Size | Bleed? | Back Page? | Text Format | Need? | Size | *Language Protocol | Size | |
| 13 | 1 | 9138000 | 500 | - SELECT ONE - | - SELECT ONE - | Single Stuff | #10.5 Window | |||
| 14 | 2 | 9138001 | 1000 | Double Stuff | 6 x 10 Window |
<tbody>
</tbody>
Please excuse my long hand. I know this is ridiculously extra and not the easiest way to accomplish this, but since it is not a range of cells indexing or lookup functions wouldn’t work, I keep getting too many function errors on any If/And functions, and I don’t know VBA to try and make it work that way. I just need to find a way to make sure the correct envelope size is being chosen automatically per project specs so that I can avoid having to request reprints for account people who can’t seem to figure this out. Please help!
Thank you in advance for any assistance rendered!
