I have 9 dropdown columns on a form and am trying to create a calculated column KPI to show red if ANY of the drop downs are set as "tasked" or "pending". i cant seem to create a proper formula.

i can make the kpi appear. i just need the condition formula

  • Can you post the formula which you have tried?
    – Keerthi
    Aug 11, 2015 at 15:47
  • I deleted after it didnt work. I have a difficult time with the And/or since it is including 9 columns in either status.
    – Jackie
    Aug 11, 2015 at 16:20

1 Answer 1


This should get you started

Concatenate all Columns then check for a string

            ISNUMBER( SEARCH("pending",C1&C2&C3&C4&C5&C6&C7&C8&C9) )
            ISNUMBER( SEARCH("tasked" ,C1&C2&C3&C4&C5&C6&C7&C8&C9) )
        , "red"
        , "green"

You have to used FIND instead of SEARCH if you want case-sensitive search. Whole Function list at https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

Please mark as answered (when you agree with the answer) to prevent your question popping up in the Unanswered list

Update after comment

Great! You found a Formula yourself, that is the best way to learn.

I have done too much JavaScript lately, corrected (and checked) my answer above.

I changed your solution a bit to inspire you more

    ="<DIV><img src='/_layouts/images/KPIDefault-"
  • The OR function returns TRUE/FALSE no need to compare with TRUE
  • moved the path before the calculation, saves some typing and easier to maintain
  • The DIV is not really needed
  • thanks! i added this with each of my column names and have #VALUE! in the column fields
    – Jackie
    Aug 11, 2015 at 17:16
  • I ended up figuring it out on my own. It may not be the most efficient way to write it, but it works: ="<DIV><img src='"&IF(OR(C1="TASKED",C1="PENDING",C2="TASKED",C2="PENDING",C3="TASKED",C3="PENDING",C4="TASKED",C4="PENDING",C5="TASKED",C5="PENDING",C6="TASKED",C6="PENDING",C7="TASKED",C7="SPENDING",C8="TASKED",C8="PENDING",C9="TASKED",C9="PENDING")=TRUE,"/_layouts/images/KPIDefault-2.GIF","/_layouts/images/KPIDefault-0.GIF")&"'></DIV>"
    – Jackie
    Aug 11, 2015 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.