Status Update -- Auto Update the icon with a Rule

Mike Wilday

Progress!!! Here is more information on the Invalid Operation error. 

https://help.smartsheet.com/articles/2476176-formula-error-messages#invalidoperation

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

And the bold text there is your culprit... try this: 

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

Paul Newcome

I thought it still needed the square brackets because it had a symbol...?

Paul Newcome

I swear we're all going to need a strong drink after this one... Haha ?

Mike Wilday

Ah, I think you're right. When I tested it let me choose it without the brackets, but then it auto-added them in. 

Paul Newcome

Ok. Just wanted to make sure. As I understand it anything with a space, number, or special character requires the square brackets. The only column names that do not are those that are a simple string of text.

 

I take advantage of this when building more complex automation with a lot of helper columns. I will name everything without any spaces, numbers, or characters so I don't forget a bracket when writing my formulas. 

 

After everything is built and working, I go through and change the column names to what I want, and the formulas will automatically update accordingly to include adding in necessary brackets.

Mike Wilday

Several. 

tania.cubell81056

Hi Mike and Paul -- cheers to you both for helping.   

Tried it out and tweaked a bit on %ages

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

If you have any ideas on better way to tweak the %ages feel free to chime in.

 

Have a wonderful weekend and again many thanks!!!!

Paul Newcome

Happy to help! 

Mike Wilday

That was a doozy. Glad we helped you figure it out!