Status Update -- Auto Update the icon with a Rule
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
Comments
-
Paul Newcome ✭✭✭✭✭✭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
-
Mike Wilday ✭✭✭✭✭✭Keep the space in the % complete as that is probably the actual column title.
-
Paul Newcome ✭✭✭✭✭✭
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)
-
Mike Wilday ✭✭✭✭✭✭
Spread out your column for %complete. You might find a space in there.
-
Mike Wilday ✭✭✭✭✭✭
spread out = make it wider so the title is on one line.
-
Paul Newcome ✭✭✭✭✭✭
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@rowranges 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.
-
Paul Newcome ✭✭✭✭✭✭
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?
-
Mike Wilday ✭✭✭✭✭✭
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"))))
-
Mike Wilday ✭✭✭✭✭✭
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
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
