一个bility to "lock" rows based on a response in a cell reference

WorkisWork
WorkisWork ✭
edited 12/09/19 inFormulas and Functions

Looking for a way to write a formula to lock rows if a cell has a specific value in it, or hide the rows.

一个ny ideas?

Tags:
· Share on Facebook Share on Twitter

Comments

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公式手册模板!
@CJohnes<\/a> <\/p>

Delete all equals in front of \"IF\" except the first equal.<\/p>

=IF([cover class]@row = \"1: 0-5%\", acres@row * 0.025, IF([cover class]@row = \"2: 6-25%\", acres@row * 0.155,<\/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":112310,"type":"question","name":"Formula help! If cell is not blank, return value from another cell","excerpt":"I'm having trouble building a formula that works for the below requirement (see screenshot for reference). If the cell in column 'INT \/ DOM \/ TEMP \/ NG' is NOT BLANK, then I need the column 'TITLE (Formula)' to pull through the value from a specific cell ('A1' in Excel terms, or 'TITLE \/ Security Title' in the example…","snippet":"I'm having trouble building a formula that works for the below requirement (see screenshot for reference). If the cell in column 'INT \/ DOM \/ TEMP \/ NG' is NOT BLANK, then I need…","categoryID":322,"dateInserted":"2023-10-28T18:43:11+00:00","dateUpdated":null,"dateLastComment":"2023-10-28T20:37:22+00:00","insertUserID":169217,"insertUser":{"userID":169217,"name":"liammac1988","url":"https:\/\/community.smartsheet.com\/profile\/liammac1988","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3YDDvy0yk2Q!hM9D4cEA5Ho!uZBTZKyzAk6","dateLastActive":"2023-10-28T21:22:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":146304,"lastUser":{"userID":146304,"name":"KPH","url":"https:\/\/community.smartsheet.com\/profile\/KPH","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BILygZsiKro!BhawQTdaN4g!kpsDfoN5YKE","dateLastActive":"2023-10-28T22:17:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3397045833,"url":"https:\/\/community.smartsheet.com\/discussion\/112310\/formula-help-if-cell-is-not-blank-return-value-from-another-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112310\/formula-help-if-cell-is-not-blank-return-value-from-another-cell","format":"Rich","lastPost":{"discussionID":112310,"commentID":402154,"name":"Re: Formula help! If cell is not blank, return value from another cell","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402154#Comment_402154","dateInserted":"2023-10-28T20:37:22+00:00","insertUserID":146304,"insertUser":{"userID":146304,"name":"KPH","url":"https:\/\/community.smartsheet.com\/profile\/KPH","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BILygZsiKro!BhawQTdaN4g!kpsDfoN5YKE","dateLastActive":"2023-10-28T22:17:49+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\/5YXSNHCRB0I2\/screenshot-2023-10-28-at-7-27-34-e2-80-afpm.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/5YXSNHCRB0I2\/screenshot-2023-10-28-at-7-27-34-e2-80-afpm.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/5YXSNHCRB0I2\/screenshot-2023-10-28-at-7-27-34-e2-80-afpm.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/5YXSNHCRB0I2\/screenshot-2023-10-28-at-7-27-34-e2-80-afpm.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/5YXSNHCRB0I2\/screenshot-2023-10-28-at-7-27-34-e2-80-afpm.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/5YXSNHCRB0I2\/screenshot-2023-10-28-at-7-27-34-e2-80-afpm.png"},"alt":"Screenshot 2023-10-28 at 7.27.34 pm.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-28T20:32:16+00:00","dateAnswered":"2023-10-28T20:04:55+00:00","acceptedAnswers":[{"commentID":402151,"body":"

Hi<\/p>

I believe this formula, entered in the TITLE (Formula) column would work for you:<\/p>

=IF(ISBLANK([INT \/ DOM \/ TEMP \/ NG]@row), \"//www.santa-greenland.com/community/discussion/33611/\", TITLE$1)<\/p>


<\/p>

It creates this:<\/p>

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


<\/p>

You start with an IF<\/p>

For the logical expression you use ISBLANK<\/p>

ISBLANK([INT \/ DOM \/ TEMP \/ NG]@row) is checking that the cell in the column INT \/ DOM \/ TEMP \/ NG is blank<\/p>

If that is true it puts in \"//www.santa-greenland.com/community/discussion/33611/\" (ie nothing)<\/p>

If it is false it puts in the value in the TITLE column, but instead of TITLE@row, it pulls just from row 1 because of the $1<\/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":112287,"type":"question","name":"Count two columns of a sheet that each match specific criteria","excerpt":"Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the Status is \"Approved\" in another column.","snippet":"Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the…","categoryID":322,"dateInserted":"2023-10-27T18:08:34+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T20:42:18+00:00","insertUserID":169195,"insertUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169195,"lastUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3396870652,"url":"https:\/\/community.smartsheet.com\/discussion\/112287\/count-two-columns-of-a-sheet-that-each-match-specific-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112287\/count-two-columns-of-a-sheet-that-each-match-specific-criteria","format":"Rich","lastPost":{"discussionID":112287,"commentID":402120,"name":"Re: Count two columns of a sheet that each match specific criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402120#Comment_402120","dateInserted":"2023-10-27T20:42:18+00:00","insertUserID":169195,"insertUser":{"userID":169195,"name":"Bob Schweighart","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Schweighart","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:41:29+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-10-27T21:43:37+00:00","dateAnswered":"2023-10-27T20:13:32+00:00","acceptedAnswers":[{"commentID":402113,"body":"

@Bob Schweighart<\/a> you just need the COUNTIFS formula and put those 2 criterion in it. Are they distinct counts for the 4 different projects? If so, that is easier. If you are counting all 4, then you need to decide on an imbedded OR or a NOT.<\/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

  • IF function to return result based on drop downs not working

    I have a column with 6 different drop down values ("cover class") and want to multiply the "acres" column by a different value depending on what was selected in "cover class." This formula only works if the first value ("1: 0-5%") is selected, otherwise it returns "#invalid operation." I know this is clunky. Any help is…

  • Formula help! If cell is not blank, return value from another cell

    I'm having trouble building a formula that works for the below requirement (see screenshot for reference). If the cell in column 'INT / DOM / TEMP / NG' is NOT BLANK, then I need the column 'TITLE (Formula)' to pull through the value from a specific cell ('A1' in Excel terms, or 'TITLE / Security Title' in the example…

  • Count two columns of a sheet that each match specific criteria

    Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the Status is "Approved" in another column.