Status Update -- Auto Update the icon with a Rule

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)

Updating Status Icon Column with %Complete and Finish.PNG

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.

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"))))