Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Etaussig

Help with ISPICKVAL IF AND FORMULA

I am trying to write a formula which compares 2 criteria, for quarterly reporting purposes.  One is a picklist value and one is a text formula field.  The scenario is this:

If ISPICKVAL = "MGM" and EXHIBITION MONTH = "JAN" THEN "MAR", EXHIBITION MONTH

OR

If ISPICKVAL = "MGM" and EXHIBITION MONTH = "FEB" THEN "MAR", EXHIBITION MONTH

OR

If ISPICKVAL = "MGM" and EXHIBITION MONTH = "APR" THEN "JUN", EXHIBITION MONTH...etc

Steve :-/

IF(AND(ISPICKVAL( Picklist , "A"), FormulaField = "A"), "Result A",

IF(AND(ISPICKVAL( Picklist , "B"), FormulaField = "B"), "Result B",

IF(AND(ISPICKVAL( Picklist , "C"), FormulaField = "C"), "Result C",

FormulaField )))

Message Edited by Stevemo on 01-19-2010 06:04 PM

Steve :-/

Sounds like you want a formula something like this:

IF(AND(ISPICKVAL( Picklist , "A"), FormulaField = "A"), "Result A",

IF(AND(ISPICKVAL( Picklist , "B"), FormulaField = "B"), "Result B",

IF(AND(ISPICKVAL( Picklist , "C"), FormulaField = "C"), "Result C",

FormulaField )))

Message Edited by Stevemo on 01-19-2010 03:19 PM
Etaussig
I Can't see your formula :)
Steve :-/

IF(AND(ISPICKVAL( Picklist , "A"), FormulaField = "A"), "Result A",

IF(AND(ISPICKVAL( Picklist , "B"), FormulaField = "B"), "Result B",

IF(AND(ISPICKVAL( Picklist , "C"), FormulaField = "C"), "Result C",

FormulaField )))

Message Edited by Stevemo on 01-19-2010 06:04 PM
This was selected as the best answer
Etaussig
Yes. I can see it.  I'll give it shot.  Thanks so much!
Etaussig
Thanks Stevemo!!!  That worked perfectly.
Etaussig

Ok.  SO now I have a new problem.  Stevemo's solution worked great, here is what I have:

 IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "JAN"), "MAR",IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "FEB"), "MAR",IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "APR"), "JUN", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "MAY"), "JUN", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "JUL"), "SEP", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "AUG"), "SEP", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "OCT"), "DEC", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "NOV"), "DEC",EXHIBITION_MONTH__c ))))))))

But now I need to do this for 5 different studios, and when I add the second studio, I get the error message:

Error: Compiled formula is too big to execute (6,554 characters). Maximum size is 5,000 characters

Does anyone have any thoughts as to how I can make this formula smaller?  Maybe using Contains?

Thanks in advance for any help.

IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c = "JAN"), "MAR",IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "FEB"), "MAR",IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "APR"), "JUN",  IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "MAY"), "JUN",  IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "JUL"), "SEP",  IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "AUG"), "SEP",  IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "OCT"), "DEC", IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "NOV"), "DEC",

IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c = "JAN"), "MAR",IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "FEB"), "MAR",IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "APR"), "JUN",  IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "MAY"), "JUN",  IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "JUL"), "SEP",  IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "AUG"), "SEP",  IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "OCT"), "DEC", IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "NOV"), "DEC",

EXHIBITION_MONTH__c ))))))))))))))))

Steve :-/
Yikes!!!  could you try re-posting that using the Code Clipboard?  That helps eliminate all of the smiley faces  caused by right quotes and commas. Also, you should try re-posting this as a new question on the new Community Site https://sites.secure.force.com/answers/ideaList?c=09a30000000D9y3 Since this is really a separate issue to the one originally posted here.
Message Edited by Stevemo on 01-22-2010 01:50 PM
Etaussig
Thanks Stevemo.  I followed your advice and posted on the other site.
Tarun Suri
hi, i am trying this example on my org. and trying to set the validation rule on 2 picklists but its giving me error
Error: Field State_and_Provision__c is a picklist field. Picklist fields are only supported in certain functions.

`IF( AND( ISPICKVAL( Country__c , "Canada"), State_and_Provision__c = "Toronto"), "Calgary", IF( AND( ISPICKVAL( Country__c , "Montreal"), State_and_Provision__c = "Toronto"), "Calgary",State_and_Provision__c ))`

pls help
Dharmin Kansara

@steve Can you help me?

I want to create a formula which is a combination of IF statement and maybe Ispickval!

The logic should be:

If owner Id = "112121212!2" than update a picklist value called "Apple" from the field Fruits

Hello,
I´m trying something similar but depending on my picklist value i wnat to show an image. Currently Saysing I'm missing ")" but I have counted and I can't find the error.

IF(
ISPICKVAL( Kundenloyalitaet__c , "sehr hoch") ,

IF
(ISPICKVAL( Kundenloyalitaet__c , "hoch") ,

IF
(ISPICKVAL( Kundenloyalitaet__c , "mittel") ,

IF
(ISPICKVAL( Kundenloyalitaet__c , "gering") ,