Long formula for Status balls with 2 criteria
I have a tracker that needs a Status column using Harvey balls.
There are columns for 6 mon, 12 mon, and 24 mon MIN and MAX and Completed Visit scheduling dates, and the Project Manager is asking for a Status column that will show whether there is a visit past due, coming up, or completed based on the colored balls.
(I struggle with creating complex formulas in the right order so that they work).
The formula needs to:
Best Answer
-
Paul Newcome ✭✭✭✭✭✭Give this a try:
I would start by inserting a date type column (called "Next Visit" in this example) and then use this formula...
=IF([Database Status]@row <> "Early Withdrawal", IF([Completed 6]@row = "", [Max 6]@row, IF([Completed 12]@row = "", [Max 12]@row, IF([Completed 24]@row = "", [Max 24]@row))))
This should give you the date of the next visit and be blank if it is either early withdrawal or the 24 month has been completed.
This should greatly simplify the Harvey Ball formula to something along the lines of...
=IF([Database Status]@row = "Early Withdrawal", "Blue", IF([Next Visit]@row = "", "Green", IF([Next Visit]@row< TODAY(), "Red", IF([Next Visit]@row<= TODAY(30), "Yellow"))))
This will be blank for any rows where the next visit is more than 30 days in the future. If you wanted to add a color for that, you would drop this in after "Yellow" and before the closing parenthesis:
"Yellow", "color for 31+ days"))))
thinkspi.com
Answers
-
Paul Newcome ✭✭✭✭✭✭
Give this a try:
I would start by inserting a date type column (called "Next Visit" in this example) and then use this formula...
=IF([Database Status]@row <> "Early Withdrawal", IF([Completed 6]@row = "", [Max 6]@row, IF([Completed 12]@row = "", [Max 12]@row, IF([Completed 24]@row = "", [Max 24]@row))))
This should give you the date of the next visit and be blank if it is either early withdrawal or the 24 month has been completed.
This should greatly simplify the Harvey Ball formula to something along the lines of...
=IF([Database Status]@row = "Early Withdrawal", "Blue", IF([Next Visit]@row = "", "Green", IF([Next Visit]@row< TODAY(), "Red", IF([Next Visit]@row<= TODAY(30), "Yellow"))))
This will be blank for any rows where the next visit is more than 30 days in the future. If you wanted to add a color for that, you would drop this in after "Yellow" and before the closing parenthesis:
"Yellow", "color for 31+ days"))))
thinkspi.com
-
RGarrels ✭Perfect!! Thank you!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
If I understand correctly, this may help.<\/p>
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})<\/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":107116,"type":"question","name":"I'd like to create a column formula that references a specific cell with acceptable syntax","excerpt":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date. My formula is currently: =[Target End Date]1 - ([Task…","snippet":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to…","categoryID":322,"dateInserted":"2023-06-29T23:48:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T22:48:54+00:00","insertUserID":163028,"insertUser":{"userID":163028,"name":"jcabaniss","url":"https:\/\/community.smartsheet.com\/profile\/jcabaniss","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T23:23:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129958,"lastUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-06-30T22:42:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":30,"score":null,"hot":3376249626,"url":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","format":"Rich","lastPost":{"discussionID":107116,"commentID":383416,"name":"Re: I'd like to create a column formula that references a specific cell with acceptable syntax","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383416#Comment_383416","dateInserted":"2023-06-30T22:48:54+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-06-30T22:42:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T15:52:25+00:00","dateAnswered":"2023-06-30T00:15:36+00:00","acceptedAnswers":[{"commentID":383245,"body":"
