Syntax Help

[TWienholz]

I'm using the following formula to automatically determine a form specification based on whether the user selects custom questions or custom questions in conjunction with a specific client but it keeps returning an error message that only tells me there is a problem with the formula. It's probably right in front of me, but I can't seem to find the syntax error in the formula. Can anyone help?

=if('Project Data Sheet'!J43="","CORE SURVEY ONLY - NO CQs"),if(and(or('Project Data Sheet'!J43>0),or('Project Data Sheet'!B4={"Alameda Alliance for Health","AultCare Health Plans","Avmed Inc.","BCBS of Massachusetts","BCBS of Vermont","BMC HealthNet","Brighton Marine Health Care","Care1st Health Plan","CareSource","Christus Health","Clover Health","Enterprise Services/DCX Tech","Fallon Health","Gateway Health Plan","Geisinger Health Plan","Harbor Health Plan","Harvard Pilgrim Healthcare","Hawaii Medical (HMSA)","Health New England","Health Partners Plans","Highmark Health Options","Highmark Health","Horizon BCBS New Jersey","Inland Empire Health Plan (IEHP)","McLaren Health Plan","MetroPlus","Molina","PacificSource Health Plan","Priority Health","QualChoice","Regence Blue Shield","St. Vincent Medical Centers","Tufts Associated Health Plans","Tufts Health Public Plans","UPMC Health Plan","Upper Peninsula Health Plan","Virgina Premier Health Plan","VNS Choice",WellCare Health Plans","Wellmark BCBS of Iowa"}),"CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT")))

Thanks!

 

[Fluff]

Try
=IF('Project Data Sheet'!J43="","CORE SURVEY ONLY - NO CQs",IF(AND(OR('Project Data Sheet'!J43>0),OR('Project Data Sheet'!B4={"Alameda Alliance for Health","AultCare Health Plans","Avmed Inc.","BCBS of Massachusetts","BCBS of Vermont","BMC HealthNet","Brighton Marine Health Care","Care1st Health Plan","CareSource","Christus Health","Clover Health","Enterprise Services/DCX Tech","Fallon Health","Gateway Health Plan","Geisinger Health Plan","Harbor Health Plan","Harvard Pilgrim Healthcare","Hawaii Medical (HMSA)","Health New England","Health Partners Plans","Highmark Health Options","Highmark Health","Horizon BCBS New Jersey","Inland Empire Health Plan (IEHP)","McLaren Health Plan","MetroPlus","Molina","PacificSource Health Plan","Priority Health","QualChoice","Regence Blue Shield","St. Vincent Medical Centers","Tufts Associated Health Plans","Tufts Health Public Plans","UPMC Health Plan","Upper Peninsula Health Plan","Virgina Premier Health Plan","VNS Choice","WellCare Health Plans","Wellmark BCBS of Iowa"})),"CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT"))

You were missing a " and also One of the ) was in the wrong place

 

[TWienholz]

Try
=IF('Project Data Sheet'!J43="","CORE SURVEY ONLY - NO CQs",IF(AND(OR('Project Data Sheet'!J43>0),OR('Project Data Sheet'!B4={"Alameda Alliance for Health","AultCare Health Plans","Avmed Inc.","BCBS of Massachusetts","BCBS of Vermont","BMC HealthNet","Brighton Marine Health Care","Care1st Health Plan","CareSource","Christus Health","Clover Health","Enterprise Services/DCX Tech","Fallon Health","Gateway Health Plan","Geisinger Health Plan","Harbor Health Plan","Harvard Pilgrim Healthcare","Hawaii Medical (HMSA)","Health New England","Health Partners Plans","Highmark Health Options","Highmark Health","Horizon BCBS New Jersey","Inland Empire Health Plan (IEHP)","McLaren Health Plan","MetroPlus","Molina","PacificSource Health Plan","Priority Health","QualChoice","Regence Blue Shield","St. Vincent Medical Centers","Tufts Associated Health Plans","Tufts Health Public Plans","UPMC Health Plan","Upper Peninsula Health Plan","Virgina Premier Health Plan","VNS Choice","WellCare Health Plans","Wellmark BCBS of Iowa"})),"CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT"))

You were missing a " and also One of the ) was in the wrong place

OMG thank you and your eyes SO much! lol I've been looking at this for 3 days and couldn't see it. Your fix worked!

 

[jtakw]

Hi,

You also have an extra OR function that's not needed:

=IF('Project Data Sheet'!J43="","CORE SURVEY ONLY - NO CQs",IF(AND('Project Data Sheet'!J43>0,OR('Project Data Sheet'!B4={"Alameda Alliance for Health","AultCare Health Plans","Avmed Inc.","BCBS of Massachusetts","BCBS of Vermont","BMC HealthNet","Brighton Marine Health Care","Care1st Health Plan","CareSource","Christus Health","Clover Health","Enterprise Services/DCX Tech","Fallon Health","Gateway Health Plan","Geisinger Health Plan","Harbor Health Plan","Harvard Pilgrim Healthcare","Hawaii Medical (HMSA)","Health New England","Health Partners Plans","Highmark Health Options","Highmark Health","Horizon BCBS New Jersey","Inland Empire Health Plan (IEHP)","McLaren Health Plan","MetroPlus","Molina","PacificSource Health Plan","Priority Health","QualChoice","Regence Blue Shield","St. Vincent Medical Centers","Tufts Associated Health Plans","Tufts Health Public Plans","UPMC Health Plan","Upper Peninsula Health Plan","Virgina Premier Health Plan","VNS Choice","WellCare Health Plans","Wellmark BCBS of Iowa"})),"CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT"))

And, might I suggest, with such a long list of OR criterion for B4, why not make a list somewhere in your sheet and check B4 against this list, that way, in case the criterion changes, you can just update the "List", which makes it Much easier to maintain and the formula becomes way "cleaner", this version of formula assumes the list at X1:X100

=IF('Project Data Sheet'!J43="","CORE SURVEY ONLY - NO CQs",IF(AND('Project Data Sheet'!J43>0,COUNTIF('Project Data Sheet'!$X$1:$X$100,'Project Data Sheet'!B4)),"CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT"))

 

[TWienholz]

Hi,

You also have an extra OR function that's not needed:

=IF('Project Data Sheet'!J43="","CORE SURVEY ONLY - NO CQs",IF(AND('Project Data Sheet'!J43>0,OR('Project Data Sheet'!B4={"Alameda Alliance for Health","AultCare Health Plans","Avmed Inc.","BCBS of Massachusetts","BCBS of Vermont","BMC HealthNet","Brighton Marine Health Care","Care1st Health Plan","CareSource","Christus Health","Clover Health","Enterprise Services/DCX Tech","Fallon Health","Gateway Health Plan","Geisinger Health Plan","Harbor Health Plan","Harvard Pilgrim Healthcare","Hawaii Medical (HMSA)","Health New England","Health Partners Plans","Highmark Health Options","Highmark Health","Horizon BCBS New Jersey","Inland Empire Health Plan (IEHP)","McLaren Health Plan","MetroPlus","Molina","PacificSource Health Plan","Priority Health","QualChoice","Regence Blue Shield","St. Vincent Medical Centers","Tufts Associated Health Plans","Tufts Health Public Plans","UPMC Health Plan","Upper Peninsula Health Plan","Virgina Premier Health Plan","VNS Choice","WellCare Health Plans","Wellmark BCBS of Iowa"})),"CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT"))

And, might I suggest, with such a long list of OR criterion for B4, why not make a list somewhere in your sheet and check B4 against this list, that way, in case the criterion changes, you can just update the "List", which makes it Much easier to maintain and the formula becomes way "cleaner", this version of formula assumes the list at X1:X100

=IF('Project Data Sheet'!J43="","CORE SURVEY ONLY - NO CQs",IF(AND('Project Data Sheet'!J43>0,COUNTIF('Project Data Sheet'!X$1:X$100,'Project Data Sheet'!B4)),"CUSTOM QUESTIONS FOR QRM REVIEW","CUSTOM QUESTIONS AUDIT"))

Oh wow! Thanks so much! My co-worker just reminded me that this list may change next season so you just saved me a ton of edits! You are awesome!

 

[Fluff]

OMG thank you and your eyes SO much! lol I've been looking at this for 3 days and couldn't see it. Your fix worked!

You're welcome & thanks for the feedback

 

[jtakw]

Oh wow! Thanks so much! My co-worker just reminded me that this list may change next season so you just saved me a ton of edits! You are awesome!

You're welcome.

If you already have all the OR criterion in an existing list for "drop down" selections, you can just use the Same list for the formula.