#Blocked when trying to delete column
I have a column "INACTIVE" that I'm trying to delete because I don't need it anymore. However, when I do, a different column that has a formula in it changes to "#BLOCKED." The "inactive" column is not part of the formula. I don't understand why deleting this column is causing this other column to be impacted. Below is the formula. Does anyone have any thoughts?
=IF(Ineligible@row = 1, "Red", IF([Launch Date]@row = "", "Yellow", IF(AND([Launch Date]@row < TODAY(), Engaged@row = 1), "Green", IF([Launch Date]@row > TODAY(), "Red", "Yellow"))))
Answers
-
Sandra Guzman ✭✭✭✭✭✭
The BLOCKED message is supposed to be an indication that a cell your are referencing contains an error. The best first step would be to check the cells you are referencing for any potential errors.
It does seem odd that your deleting the INACTIVE column would cause this error. Is the INACTIVE column feeding a formula that is tied into the Ineligible column that is referenced in your formula by chance?
If all the cells listed in your formula have the same naming convention and do not have any errors, then you might want to add a new column and paste your formula in there to see if you get it to work. I have had a few instances where for whatever reason my formulas don't work as expected. Clearing the contents of the cell or adding a new column has helped to resolve some of those intermittent issues. It's worth giving it a try.
All the best,
Sandra
-
Mike Wilday ✭✭✭✭✭✭
See this great post on Formula errors which indicates what the #Blocked error means.
I agree with Sandra, the column showing the message must be referencing your Inactive column.
Help Article Resources
Categories
=if(countif([due date]:[due date],<today()) = 0,\"Green\",if(count([due date]:[due date]) * .1 >countif([due date]:[due date],<today()),\"Yellow\",\"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":[]},{"discussionID":111265,"type":"question","name":"Calculating fines due based on calendar date","excerpt":"Hi, I am looking for help with calculating fines based on a missed due date, and the subsequent number of calendar days past the due date. Ideally, I would like the fine to calculate in two instances. 1) if the date in \"Y1Q1 Date Payment RCVD\" is greater than the due date, OR 2). the date is blank and today's date is…","snippet":"Hi, I am looking for help with calculating fines based on a missed due date, and the subsequent number of calendar days past the due date. Ideally, I would like the fine to…","categoryID":322,"dateInserted":"2023-10-05T17:03:50+00:00","dateUpdated":null,"dateLastComment":"2023-10-05T18:35:56+00:00","insertUserID":168047,"insertUser":{"userID":168047,"name":"Stitched","url":"https:\/\/community.smartsheet.com\/profile\/Stitched","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T18:27:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-10-05T20:38:02+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":17,"score":null,"hot":3393058186,"url":"https:\/\/community.smartsheet.com\/discussion\/111265\/calculating-fines-due-based-on-calendar-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111265\/calculating-fines-due-based-on-calendar-date","format":"Rich","lastPost":{"discussionID":111265,"commentID":398719,"name":"Re: Calculating fines due based on calendar date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398719#Comment_398719","dateInserted":"2023-10-05T18:35:56+00:00","insertUserID":120260,"insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-10-05T20:38:02+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"}},"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\/M47QMCUFOQU2\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-05T20:33:37+00:00","dateAnswered":"2023-10-05T17:40:08+00:00","acceptedAnswers":[{"commentID":398693,"body":"
I think this will work. The first part of the formula handles if a payment was received but was past the due date, and the second part handles if TODAY() is past the due date but no payment shows as received. It should return blank if neither requirements are met, so it inherently solves the third example.<\/p>
Formula<\/strong><\/p> =IF(NOT(ISBLANK([Y1Q1 Date Payment RCVD]@row)), IF([Y1Q1 Date Payment RCVD]@row > [Due Date]@row, [Y1Q1 Date Payment RCVD]@row - [Due Date]@row), IF(TODAY() > [Due Date]@row, TODAY() - [Due Date]@row))<\/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":111252,"type":"question","name":"Convert Text to Date","excerpt":"I am looking for help to convert 20230818 ['Max Date' text formatted column] to read 08\/18\/2023 in the ['Last Activity Date' date formatted column]. However, the formula that I am using returns a value of #INCORRECT ARGUMENT SET. =DATE(VALUE(MID([Max Date]@row, 5, 2), (VALUE(RIGHT([Max Date]@row, 2), (VALUE(LEFT([Max…","snippet":"I am looking for help to convert 20230818 ['Max Date' text formatted column] to read 08\/18\/2023 in the ['Last Activity Date' date formatted column]. However, the formula that I am…","categoryID":322,"dateInserted":"2023-10-05T14:43:03+00:00","dateUpdated":null,"dateLastComment":"2023-10-05T16:30:55+00:00","insertUserID":165028,"insertUser":{"userID":165028,"name":"Heather P.","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Heather%20P.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jQ9z43MAzno!g06wZ9dLOEg!WMaAtvFiz-H","dateLastActive":"2023-10-05T20:23:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":142964,"lastUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-10-05T20:45:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":31,"score":null,"hot":3393043438,"url":"https:\/\/community.smartsheet.com\/discussion\/111252\/convert-text-to-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111252\/convert-text-to-date","format":"Rich","lastPost":{"discussionID":111252,"commentID":398665,"name":"Re: Convert Text to Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398665#Comment_398665","dateInserted":"2023-10-05T16:30:55+00:00","insertUserID":142964,"insertUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-10-05T20:45:58+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\/XEL3OVI7TIKD\/snag-853419.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png"},"alt":"Snag_853419.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-05T15:59:30+00:00","dateAnswered":"2023-10-05T15:14:46+00:00","acceptedAnswers":[{"commentID":398634,"body":"