Find your content:

Search form

You are here

Can you use the CASE formula to show values NOT SELECTED in a picklist?


I'm trying to come up with a formula that will show the remaining picklist values that have not been selected by the user as the "remaining" choices. I'm using the formula listed below but unfortunately once I finalize the formula, I have over 5,000 characters. EVeryone says I should utilize the CASE formula but I have not been able to make it work the way I need it. See below for an example of what formula I'm trying to create.

IF(ISPICKVAL(Program_Type__c , "Accessories - ACH"), IF( !INCLUDES(Paperwork_Completed1__c , "App for Dealer Status"), "App for Dealer Status" &BR() , null) & IF( !INCLUDES(Paperwork_Completed1__c , "Copy of Driver's License"), "Copy of Driver's License" &BR() , null) & IF( !INCLUDES(Paperwork_Completed1__c , "Copy of Voided Check"), "Copy of Voided Check" &BR() , null) & IF( !INCLUDES(Paperwork_Completed1__c , "ACH Authorization"), "ACH Authorization" &BR() , null) & IF( !INCLUDES(Paperwork_Completed1__c , "Resale Certificate"), "Resale Certificate" &BR() , null) & IF( !INCLUDES(Paperwork_Completed1__c , "Terms and Conditions"), "Terms and Conditions", null)& IF( !INCLUDES(Paperwork_Completed1__c , "W-9"), "W-9", null),

Attribution to: Robin Terrell

Possible Suggestion/Solution #1

I don't think it is possible to use a CASE with multi-select picklist so I think your best bet is to try to shorten the formula. A few ideas:

  1. See if you can abbreviate the text being compared. Instead of "ACH - Authorization" maybe you can use "ACH-Auth", which would save 11 characters.
  2. Use a numbering system. Have your picklist values be "1: ACH-Auth", "2: Voided Check" and then your formula can just return the step numbers, "1, 2" saving characters.
  3. Combine ideas #1 and #2

Attribution to: Mike Chale

Possible Suggestion/Solution #2

While this is not a great solution and will work dependant on the number of picklist values in your field (as the formula length will be proportional to number of Picklist values), if there aren't too many, here's how you can do it with case.

This is done for simplicity with the Rating field on Account, which has values {Hot, Warm, Cold}

"Hot", "Warm, Cold",   //if the value is Hot, then return "Warm, Cold"
"Warm", "Hot, Cold", 
"Cold", "Warm, Hot", 
"Hot, Warm, Cold") //Default value if no picklist value is selected

Attribution to: techtrekker

Possible Suggestion/Solution #3

This is interesting, but what's the use case? Why would you want to show the other values that are not selected?

Attribution to: Fifedog
This content is remixed from stackoverflow or stackexchange. Please visit

My Block Status

My Block Content