防止公式重写

Madison_Gipson
编辑12/09/19 公式和函数

大家好!

我设置了一个列来指示任务进度(可以从下拉菜单中选择:尚未开始,正在进行中,接近完成等),但也希望它自动标记“完成”,如果另一列(复选框格式)有一个标记的复选框。

我遇到的问题是,如果有人从下拉/类型中选择一个值,公式就会消失。这意味着当任务在复选框列中被选中为完成时,task Progress列不会更新为“完成”,用户必须手动更改它。

为了防止这种情况-有没有办法设置它到公式不被手动输入覆盖的地方?谢谢你!

评论

  • 她名叫Stara
    她名叫Stara ✭✭✭✭✭✭

    嗨,麦迪逊,

    不幸的是,目前不可能有一个公式,也手动更改值,但这是一个很好的想法!

    当你有时间的时候,请提交一个增强请求。

    一个可能的解决方案是使用一个公式来更改引用日期和其他单元格的状态。

    如果您仍然希望能够手动更改值,那么您可以使用第三方服务Zapier来完成更改值,并且它不依赖于任何公式。

    这些选择中有一个可行吗?你可以选择Zapier吗?

    我希望这对你有帮助!

    祝你度过美好的一天!

    最好的

    她名叫Stara

    工作流程顾问/ CEO @ WORK BOLD

    work-bold图像

    智能表专家顾问兼合伙人

    她名叫Stara|工作流程顾问/ CEO @工作大胆

    W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35

    请随时与我联系,了解有关Smartsheet,集成,一般工作流建议或其他方面的帮助。

  • 迈克Wilday
    迈克Wilday ✭✭✭✭✭✭

    嗨,麦迪逊,

    正如安德鲁所说,这目前是不可能的。如果你想让这个过程自动化,你必须让整个过程自动化,或者根本不自动化。对包含公式的字段的任何手动调整都将覆盖公式并使其无效。为了解决这个问题,我们的团队自动化了整个过程。我们创建了实际开始和实际完成日期列,然后使用下面的公式来指示项目的状态。

    =如果(和(Isblank((实际开始)@row),空白([实际完成])@row)),“未启动”,IF(ISBLANK([实际完成])@row),“正在进行中”,IF(ISDATE([实际完成])@row)“完成”)))

帮助文章参考资料欧宝体育app官方888

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
0, then calculate the Sum Cell b-cell a","categoryID":322,"dateInserted":"2023-06-11T00:07:43+00:00","dateUpdated":"2023-06-12T08:42:14+00:00","dateLastComment":"2023-06-12T14:32:38+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-12T14:38:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-06-12T14:31:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":67,"score":null,"hot":3373027821,"url":"https:\/\/community.smartsheet.com\/discussion\/106304\/i-need-help-writing-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106304\/i-need-help-writing-a-formula","format":"Rich","lastPost":{"discussionID":106304,"commentID":380099,"name":"Re: I need help writing a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380099#Comment_380099","dateInserted":"2023-06-12T14:32:38+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-06-12T14:31:43+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-12T14:23:01+00:00","dateAnswered":"2023-06-12T14:18:50+00:00","acceptedAnswers":[{"commentID":380096,"body":"
\n \n https:\/\/community.smartsheet.com\/discussion\/comment\/380093#Comment_380093\n <\/a>\n<\/div>\n

You would need to add another column to be able to make that calculation.<\/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":106279,"type":"question","name":"How do I create and If Formula pulling from another sheet to designate indicators?","excerpt":"Good Morning All, I am working on a Overall Health for a Project that looks like this: I need to update the lights by using an if statement looking at three different columns of another sheet that are not next to one another on the sheet, example of that sheet below In the first sheet for each Column I need to look at the…","categoryID":322,"dateInserted":"2023-06-09T16:40:18+00:00","dateUpdated":"2023-06-09T16:47:15+00:00","dateLastComment":"2023-06-12T16:33:32+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T17:31:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":162256,"lastUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T17:31:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":64,"score":null,"hot":3372921230,"url":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","format":"Rich","lastPost":{"discussionID":106279,"commentID":380135,"name":"Re: How do I create and If Formula pulling from another sheet to designate indicators?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380135#Comment_380135","dateInserted":"2023-06-12T16:33:32+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T17:31:24+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/XSK1SHR157VN\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-11T16:45:30+00:00","dateAnswered":"2023-06-11T12:13:51+00:00","acceptedAnswers":[{"commentID":380000,"body":"

If it isn't weekly, then you would use a COUNTIFS in nested IFs.<\/p>

=IF(COUNTIFS({Criteria}, @cell = \"Infrastructure\", {Due Date}, @cell<= TODAY())> 0, \"Red\", IF(.....<\/p>


<\/p>

You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.<\/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":106303,"type":"question","name":"Stumped on a formula for a parent cell to aggregate names of the subtasks.","excerpt":"I'd like to capture the Individual Project names, tracked by subtasks, and aggregate them in the Parent Cell (All Project Names). This way, when minimized, I don't have to expand to see which Sites the task is for. Also for Dashboard views, to see which Sites are part of the parent project that is being tracked. Screenshot…","categoryID":322,"dateInserted":"2023-06-10T16:49:08+00:00","dateUpdated":null,"dateLastComment":"2023-06-10T21:44:52+00:00","insertUserID":162134,"insertUser":{"userID":162134,"name":"CamSME","title":"Process Developer","url":"https:\/\/community.smartsheet.com\/profile\/CamSME","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-11T14:32:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":116407,"lastUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9WIC1TB7AJQT\/nP1GU5H4ITA1P.png","dateLastActive":"2023-06-12T16:35:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3372851040,"url":"https:\/\/community.smartsheet.com\/discussion\/106303\/stumped-on-a-formula-for-a-parent-cell-to-aggregate-names-of-the-subtasks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106303\/stumped-on-a-formula-for-a-parent-cell-to-aggregate-names-of-the-subtasks","format":"Rich","lastPost":{"discussionID":106303,"commentID":379990,"name":"Re: Stumped on a formula for a parent cell to aggregate names of the subtasks.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379990#Comment_379990","dateInserted":"2023-06-10T21:44:52+00:00","insertUserID":116407,"insertUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9WIC1TB7AJQT\/nP1GU5H4ITA1P.png","dateLastActive":"2023-06-12T16:35:41+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/OO7GA4QG90VK\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-10T17:50:36+00:00","dateAnswered":"2023-06-10T17:37:08+00:00","acceptedAnswers":[{"commentID":379986,"body":"

@CamSME<\/a> you'll use join and collect with children or decedents as the range. This video might help.<\/p>

\n \n https:\/\/youtu.be\/Dzo0UYjxMmI\n <\/a>\n<\/div>


<\/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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势