Checking if a cell has absolutely nothing in it
I have a sheet where I have needed to suppress any errors with ISERROR function. The formulas are showing blank now because division by zero errors are suppressed. The formulas will show data when data is entered in other cells. The issue is when someone adds a row to the sheet, I want to make it apparent that they forgot to copy these formulas into the new row by doing a check.
I created a column called "VALID ROW ?" with this formula =IF(OR(ISBLANK([VALUE / HOUR]7) = 1, ISBLANK([PICK NEXT]7 = 1)), "No", "Yes"), but that won't work because it's checking if the cells are blank, not empty. Is there a function that checks if a cell is completely empty, including not only blanks, but formulas that are returning blanks?
Thank you!
Comments
-
L_123 ✭✭✭✭✭✭
the isblank formula should work even when a formula is returning a blank. I'm not 100% sure what is going on, but try to take off the =1 on the criteria.
=if(or(isblank({CellRef1}),isblank({CellRef2})),1,0)
-
Paul Newcome ✭✭✭✭✭✭[email protected]is correct. If that still does not do the trick, you could change your original formula to display something instead of nothing and use conditional formatting to change the color of the text to match the color of the cell based on if that particular text is there or not. I usually just use "." for this.
This will give the cell something to count as, but will appear as blank.
You could then adjust your IF statement (I'll use my example above for this one) to something like this...
=IF(OR([VALUE / HOUR]7 = ".", [PICK NEXT]7 = ".", "No", "Yes")
Help Article Resources
Categories
Here is a possible solution for your formula:<\/p>
=IF(NOT(ISBLANK([Recovery Collected Date]@row)), \"Complete\", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), \"Incomplete\", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), \"Pending\", \"//www.santa-greenland.com/community/discussion/45421/\")))<\/p>
Please confirm I have your column names correct and adjust as needed.<\/p>
I hope this helps, and have a great weekend.<\/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":108264,"type":"question","name":"ROUNDDOWN","excerpt":"Question. I have a column that is calculating time. So in that column I get total minutes comparing a few other columns. I then want the total minutes to convert to hours and minutes. I am using this formula: =ROUNDDOWN([Total Down in Minutes]@row \/ 60, 0) + \":\" + MOD([Total Down in Minutes]@row, 60) The value it is…","snippet":"Question. I have a column that is calculating time. So in that column I get total minutes comparing a few other columns. I then want the total minutes to convert to hours and…","categoryID":322,"dateInserted":"2023-07-28T17:16:37+00:00","dateUpdated":"2023-07-28T17:17:45+00:00","dateLastComment":"2023-07-28T18:06:57+00:00","insertUserID":132978,"insertUser":{"userID":132978,"name":"David Noël","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20No%C3%ABl","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ujDCdw6fmPQ!WwFCX7KSXvo!WrOxILxFQ6w","dateLastActive":"2023-07-28T18:17:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":132978,"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-07-28T18:57:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3381134014,"url":"https:\/\/community.smartsheet.com\/discussion\/108264\/rounddown","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108264\/rounddown","format":"Rich","lastPost":{"discussionID":108264,"commentID":387877,"name":"Re: ROUNDDOWN","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387877#Comment_387877","dateInserted":"2023-07-28T18:06:57+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-07-28T18:57:09+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-07-28T18:18:15+00:00","dateAnswered":"2023-07-28T18:02:47+00:00","acceptedAnswers":[{"commentID":387875,"body":"
See if this works for you:<\/p>
=ROUNDDOWN([Total Down in Minutes]@row \/ 60, 0) + \":\" + IF(MOD([Total Down in Minutes]@row, 60) < 10, \"0\" + MOD([Total Down in Minutes]@row, 60), MOD([Total Down in Minutes]@row, 60))<\/p>"},{"commentID":387876,"body":"
Carson, thank you. This makes complete sense and hate I didn't think of the last part of that formula. Thank you again!<\/p>"},{"commentID":387877,"body":"
Happy to help!<\/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":108250,"type":"question","name":"formula help - using two cells","excerpt":"looking to fill in answer to the question - if \"Cell\" is less than one then put 1 in formula cell. in our case - we fill in the acreage of the lot. if this is less than 1 acre we need to put a \"1\" in the upgrade septic line Still learning formulas and this one is too advance for me!","snippet":"looking to fill in answer to the question - if \"Cell\" is less than one then put 1 in formula cell. in our case - we fill in the acreage of the lot. if this is less than 1 acre we…","categoryID":322,"dateInserted":"2023-07-28T14:49:49+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T17:55:24+00:00","insertUserID":164264,"insertUser":{"userID":164264,"name":"cris3","title":"Office Admin","url":"https:\/\/community.smartsheet.com\/profile\/cris3","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T17:55:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-07-28T17:54:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":18,"score":null,"hot":3381125113,"url":"https:\/\/community.smartsheet.com\/discussion\/108250\/formula-help-using-two-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108250\/formula-help-using-two-cells","format":"Rich","lastPost":{"discussionID":108250,"commentID":387874,"name":"Re: formula help - using two cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387874#Comment_387874","dateInserted":"2023-07-28T17:55:24+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-07-28T17:54:59+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-07-28T17:48:00+00:00","dateAnswered":"2023-07-28T17:21:03+00:00","acceptedAnswers":[{"commentID":387863,"body":"
Hi @cris3<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =IF(Acreage@row <> \"//www.santa-greenland.com/community/discussion/45421/\", IF(Acreage@row < 1, 1))<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic weekend!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[]}">
