Calculated Column condition formula

Question

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

Answer 1

This should get you started

Concatenate all Columns then check for a string

    =IF( 
        OR(     
            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-"
    &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="PENDING",
        C8="TASKED",C8="PENDING",
        C9="TASKED",C9="PENDING"
        ),"2","0")
    &".GIF'></DIV>"

• 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