Automatic Status (Green, Red, Yellow) depending of two Colum condition

Hi everyone!

I'm trying to configure a Health Status condition depending on two colum statuses here the formula "=IF([Health Status]@row = "Not Started", "", IF(AND([Due Date]@row < TODAY(), [Health Status]@row = "In Progress"), "Green", IF([Due Date]@row >= TODAY(), IF([Health Status]@row = "In Progress", "Red", "Green"), IF([Health Status]@row = "Complete", "Green", "Yellow"))))"


Its return a #CIRCULAR REFERENCE message

Can anyone help to find a solution?

Regards

Wilfredo Caban

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Wilfredo Caban

    You are probably either putting this formula into the [Health Status] column, or the [Health Status] or the [Due Date] column have a reference (probably formula) that's referencing back to the column you're trying to build this formula into. Here's a description of a Circular Reference error:

    #CIRCULAR REFERENCE

    Cause

    The formula references itself. The circular reference may be direct where the reference is in the formula text itself, or indirect where this formula references a cell which then references back to this cell.

    Resolution

    Determine which reference is circular. Indirect references can be many levels deep. Sometimes it is easiest to make a copy of the formula and remove cell references until the error is eliminated. This process of elimination will help you see which reference is ultimately circular.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!