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 '15 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 '15 at 16:20

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 '15 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 '15 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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