IF NOT ISBLANK help

Zachary Mitchell
Zachary Mitchell ✭
inFormulas and Functions

I'm trying to create a formula that will display the different colored dots in the status bar. I had success at first but one part of the formula isn't working with the others. I need it to display BLANK if Patient is blank, Gray if patient is filled in but Order Request Date is blank, Green if Complete date is filled in, Red if Complete is blank and return visit is within 1 week, and Yellow if Complete date is blank and return visit is within 2 weeks.

I was able to get this formula to work but when I try to input the first part so that no color shows up the whole thing fails :

=IF(ISBLANK([Order/Request Date]@row), "Gray", IF(AND(ISDATE([Current Visit Date]@row), ISDATE([Completed Date]@row)), "Green", IF(AND([Next Visit Date]@row - 7 <= TODAY(), (ISBLANK([Completed Date]@row))), "Red", IF(AND([Next Visit Date]@row - 6 >= TODAY(), (ISBLANK([Completed Date]@row))), "Yellow"))))

Also here is a screenshot of the sheet

Snip 1.png

Hope someone can help, thanks so much smartsheet community!

Tags:
· Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Hi @Jsantos<\/a> <\/p>

For the record a date automation option, it can work for both if you have separate date columns for when the row was raised and resolved. For each you'd set it for when the row was changed to the relevant status and have the automation record the date in the appropriate column.<\/p>

You can also set a row lock automation if you wish (though be aware that the sheet owner & admins can still edit the line).<\/p>

Example:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Obviously your sheet will have more columns, but the accompanying automations for this would be:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

If rows are being created when raised only, you can also used the system generated \"Created date\" column, and similarly you can use the \"Modified date\" & slightly modified lock automation if you wanted date time (changed the condition to the status being resolved).<\/p>

Hope this helps, but if you've any questions etc. then just post! 😊<\/span><\/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":319,"urlcode":"functionality","name":"functionality"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}]},{"discussionID":109234,"type":"question","name":"Formula when a column has a checkbox","excerpt":"I cannot seem to find this answer: When a column has a checkbox, another column must have data in it or it will turn red Example. If someone is needed on site (it is checked) then a person(s) name must be in the box. They cannot leave it blank or it will be red. On Site Person Contact List or cell is RED","snippet":"I cannot seem to find this answer: When a column has a checkbox, another column must have data in it or it will turn red Example. If someone is needed on site (it is checked) then…","categoryID":322,"dateInserted":"2023-08-21T20:32:43+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T12:27:19+00:00","insertUserID":125555,"insertUser":{"userID":125555,"name":"Gina Prego","url":"https:\/\/community.smartsheet.com\/profile\/Gina%20Prego","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T12:07:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-22T12:31:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":23,"score":null,"hot":3385360202,"url":"https:\/\/community.smartsheet.com\/discussion\/109234\/formula-when-a-column-has-a-checkbox","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109234\/formula-when-a-column-has-a-checkbox","format":"Rich","tagIDs":[245,247,281,285,472],"lastPost":{"discussionID":109234,"commentID":391814,"name":"Re: Formula when a column has a checkbox","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391814#Comment_391814","dateInserted":"2023-08-22T12:27:19+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-22T12:31:13+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-22T12:07:20+00:00","dateAnswered":"2023-08-21T22:18:02+00:00","acceptedAnswers":[{"commentID":391742,"body":"

Try something like this:<\/p>

=IF(AND([Checkbox Column]@row = 1, [Person's Name Column]@row = \"//www.santa-greenland.com/community/discussion/65374/\"), \"Red\")<\/p>"},{"commentID":391804,"body":"

I'm getting a circular reference error. This is what I am using:<\/p>

=IF(AND([On Site]@row = 1, [Who is On Site]@row = \"//www.santa-greenland.com/community/discussion/65374/\"), \"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":245,"urlcode":"Sharing","name":"Sharing and Collaboration"},{"tagID":247,"urlcode":"Contacts","name":"Contacts"},{"tagID":281,"urlcode":"Support","name":"Support"},{"tagID":285,"urlcode":"Solution Center","name":"Solution Center"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}]},{"discussionID":109203,"type":"question","name":"What formula is best suitable for counting Yes and No and referencing column \"Assigned To\"","excerpt":"What formula is best suitable for counting Yes and No and referencing the column \"Assigned To\" name i.e Brandon = 6 \"Yes\" Cathy = 1 \"Yes\", Jimmy = 1 \"No\" and 6 \"Yes\"","snippet":"What formula is best suitable for counting Yes and No and referencing the column \"Assigned To\" name i.e Brandon = 6 \"Yes\" Cathy = 1 \"Yes\", Jimmy = 1 \"No\" and 6 \"Yes\"","categoryID":322,"dateInserted":"2023-08-21T16:05:46+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T12:27:39+00:00","insertUserID":165416,"insertUser":{"userID":165416,"name":"JimmyLarimer","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JimmyLarimer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wp8ov9fTuvs!cp9q53Po-vs!6k-VfhEC6Tz","dateLastActive":"2023-08-22T12:16:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-22T12:31:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":13,"countViews":62,"score":null,"hot":3385349005,"url":"https:\/\/community.smartsheet.com\/discussion\/109203\/what-formula-is-best-suitable-for-counting-yes-and-no-and-referencing-column-assigned-to","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109203\/what-formula-is-best-suitable-for-counting-yes-and-no-and-referencing-column-assigned-to","format":"Rich","lastPost":{"discussionID":109203,"commentID":391815,"name":"Re: What formula is best suitable for counting Yes and No and referencing column \"Assigned To\"","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391815#Comment_391815","dateInserted":"2023-08-22T12:27:39+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-22T12:31:13+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/UOAIP4NHINZ2\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T12:17:05+00:00","dateAnswered":"2023-08-21T21:46:03+00:00","acceptedAnswers":[{"commentID":391726,"body":"

There it is. Your second [%Complete] is missing the space in the column name.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • RAID Log Date Value Locking per column when status changes

    你好everyone, I have tried to find the best answer for this but I see too many threads and different responses. If anyone could help as I am new to this. I am only looking to lock in a date value on 2 columns whenever there is a status change, from when an issue is raised and then resolved. I know there is a record a…

  • Formula when a column has a checkbox

    I cannot seem to find this answer: When a column has a checkbox, another column must have data in it or it will turn red Example. If someone is needed on site (it is checked) then a person(s) name must be in the box. They cannot leave it blank or it will be red. On Site Person Contact List or cell is RED

  • What formula is best suitable for counting Yes and No and referencing column "Assigned To"

    What formula is best suitable for counting Yes and No and referencing the column "Assigned To" name i.e Brandon = 6 "Yes" Cathy = 1 "Yes", Jimmy = 1 "No" and 6 "Yes"