Status Update -- Auto Update the icon with a Rule

edited 12/09/19

hope you may assist as I've seen you post about other Status formulas.

I'm trying to write the "IF" statement to look at status update the Icon to Red, Yellow, Green or Blue

=IF((AND([% Complete]@row <> 1, TODAY() - Finish@row > 1), "Red"), IF(AND ([% Complete]@row <= .75, TODAY() - Finish@row =< -5), "Yellow"), IF(AND [% Complete]@row > .75, TODAY() - Finish@row < -5), "Green" ), IF ( [% Complete]@row = 1, "Blue") )

I'm experiencing a Syntax error

Here's what i want it to do:

1.  If %Complete <100%, and > 1 days AFTER Finish = RED

2.  If % Complete  <= 75%, and <= 5 days BEFORE FINISH = YELLOW

3.  If % Complete >75% , and >5 days BEFORE Finish = GREEN

4.  If % Complete = 100% = BLUE

Tags:
«1

• ✭✭✭✭✭✭

You have some extra parenthesis in there, some missing parenthesis, and some extra spaces. Try this and see if that gets rid of the error...

=IF(AND([% Complete]@row <> 1, TODAY() - Finish@row > 1), "Red", IF(AND ([% Complete]@row <= .75, TODAY() - Finish@row =< -5), "Yellow", IF(AND([% Complete]@row > .75, TODAY() - Finish@row < -5), "Green", IF([% Complete]@row = 1, "Blue"))))

• Hi Paul,

Thank you for the recommendation -- updated it a bit to close the space in the Column Title "%Complete"

Here's the revised formula (minus that space):

=IF(AND([%Complete]@row <> 1, TODAY() - Finish@row > 1), "Red", IF(AND ([%Complete]@row <= .75, TODAY() - Finish@row =< -5), "Yellow", IF(AND([%Complete]@row > .75, TODAY() - Finish@row < -5), "Green", IF([%Complete]@row = 1, "Blue"))))

Can't figure out where i'm going wrong -- as still throws an "Unparseable" error.

Appreciate any ideas you may have and again many thanks for responding

• ✭✭✭✭✭✭

Keep the space in the % complete as that is probably the actual column title.

• ✭✭✭✭✭✭

Your commas, parenthesis, etc. all seem to be in the right places. What are the EXACT column names in the sheet?

• %Complete
• Finish

• Attached is a screenshot with column names

Wanted to post the result in: Status Icon (column)

• ✭✭✭✭✭✭

Spread out your column for %complete. You might find a space in there.

• ✭✭✭✭✭✭

spread out = make it wider so the title is on one line.

• ✭✭✭✭✭✭
edited 03/07/19

When I referenced extra spaces in my original reply, I was referring to extra spaces between parenthesis and whatnot. Not column names. Things like... ) ) instead of )) and IF( [ instead of IF([......

If there wasn't a space between the % and Complete, it would show as "%Compl....." in the column header.

Because it is able to shift the column name into two separate lines, that means there is a space.

To help with this...

When writing your formula, click on a cell (any cell will do) within that column to auto-populate the cell reference [Column Name]#.

You can then delete the number and type in the correct row number or type in @row (whichever is needed). That will ensure your column name is definitely correct within the formula.

I use this a lot when I am doing longer formulas with @row ranges where the text bar hides the row I am on.

• checked the column names with your suggestion -- and it is %Complete -- implemented the suggestion to click on the cell to populate the formula.  Still running into the Unparseable error.

​​​​​​​

• ✭✭✭✭✭✭

Can you post a published link to the sheet with dummy data in it so we can actually get in there and take a look around to see if its maybe something we aren't thinking of?

• ✭✭✭✭✭✭

Try removing the brackets off of the %Complete title. Since there is no space, there is no need for the brackets.

So it would read %Complete@row

• Here's the formula trying to use:

IF(AND(%Complete@row < 1, Finish@row -TODAY() > 1), "Red", IF(AND (%Complete@row <= .75, TODAY() - Finish@row =< -5), "Yellow", IF(AND(%Complete@row > .75, TODAY() - Finish@row < -5), "Green", IF(%Complete@row = 1, "Blue"))))

• ✭✭✭✭✭✭

There was a space after one of the ANDs... does this one work?

=IF(AND(%Complete@row < 1, Finish@row -TODAY() > 1), "Red", IF(AND(%Complete@row <= .75, TODAY() - Finish@row =< -5), "Yellow", IF(AND(%Complete@row > .75, TODAY() - Finish@row < -5), "Green", IF(%Complete@row = 1, "Blue"))))

• Hi Mike,

Thanks for responding.  plugged in your formula and  received an "invalid operator" error.

Best,

Tania

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!