一键完成任务
你好,
在Project中，我们总是有不同的变体:
-完成百分比
-任务状态
-竣工日期
- - - - - -等
我要找的是一种方式，说我勾选“完成”复选框和:
- percent complete变为100%
—任务状态变为“完成”
-完成日期是今天
- - - - - -等
评论
-
特拉维斯 员工Hi Kite -可以根据另一个字段的值自动执行字段，但取决于您的工作表如何设置，您所要求的所有内容可能无法实现。
对于每个示例，CB是Checkbox列名，引用的单元格位于第1行。需要更新此单元格引用以匹配工作表中的单元格。
“完成百分比变为100%”
将这个公式添加到% Complete列中:
= if (cb1 = 1,1)
如果复选框(CB1)被选中(1)，这将显示100%(1)。如果您启用了依赖项，并且您的% Complete列与依赖项相关联，则您将无法向% Complete列添加公式。解决这个问题的唯一方法是将% Complete列与依赖项解关联。
"任务状态变为完成"
将这个公式添加到你的状态栏:
=IF(CB1 = 1， " Complete ")
“完成日期是今天”
您可以构建一个公式，如果复选框被选中，则显示“今天”日期，但“今天”每天都会更改。如果选中复选框是工作表中要修改的最后一项，则可以使用修改(日期)系统列它显示上次修改该行的时间戳。
相同的公式格式可用于自动化多种类型的列。这里有一篇关于RYG球自动化的支持提示文章://www.santa-greenland.com/blog/support-tip-automate-RYG-balls
这里是我们帮助中心关于公式的文章的链接，可以帮助您了解我们支持的公式:help.smartsheet.com/customer/portal/articles/775363-using-formulas
-
杰夫•肯尼迪 ✭好吧，现在有解决办法了。下面是我实现的简化版本。
基本上，只需点击一下，我就可以更新状态和%完成，以及自动设置其他状态
下面是它的工作原理
在管理员控制下的字段(即锁定给大多数用户)
-“签收”(复选框)
-“等待”(复选框)
计算锁定域
-“% Comp”(对指定人员想法的智能解释(“Est % Comp”)，但在需要时被逻辑覆盖)
“状态”(使用多个字段智能计算状态-现在不需要手动设置)
用户字段
-“Est % Comp”(被指派人员认为的完成百分比)
-“指派予”
- - - - - -等
它是如何工作的
-选中“sign - off”，然后“% Comp”= 100%，“Status”=“Complete”
-“% Comp”
-如果签到，则= 100%
-如果sign - off未选中，则使用% Compl，但不能超过99%
-即无论用户在Est % Comp中输入了什么，“% Comp”中的值永远不会大于99%，除非勾选了“签名”
—“状态”是计算出来的，等于
- "Complete" - "Signoff"已勾选
-“收到”-“分配给”为空
-“分配”-“分配到”不为空
-完成日期-决定不自动执行此操作，而是在未设置日期的情况下突出显示多个单元格的寿命。原因是我们需要对完成日期设置进行一些尽职调查
易怒的细节
公式
状态字段
=IF((Signoff5 = 1)， ([On wait]5 = 0))， "8. IF(AND(([On wait]5 = 1)， ([On wait]5 = 1))， "取消"，IF([On Hold]5 = 1， "搁置”,如果(((% Comp) 5 > 0,(分配)5 <> ""), " 4所示。在进行中"，IF([Assigned To]5 <> ""， "1。分配”、“0。收到")))))
%比较
=IF(Signoff5 = 1,1, IF([Est % Comp]5 > 0.99, 0.99， [Est % Comp]5))
条件格式
-它比这更复杂，但基本是
-如果选中“签收”，则“日期完成”和“签收”单元格将以亮红色突出显示 -
山姆年代 ✭
感谢Geoff的分享，这是一个很棒的解决方案!我将试着在我的一张表格中实现其中的一些。祝我好运吧!
-
杰夫•肯尼迪 ✭
嗨,蒂姆,
真正工作得很好的是最后的签字复选框——无论任务所有者认为他们已经完成了多少任务，它都无法进入计算完成状态，除非我勾选签字框。我每天都会得到更新，发送给我任何需要签字的任务
-
蒂姆·米克斯 ✭✭✭✭✭✭杰夫,
试图找到一个解决方案类似上面的东西。我的专栏有太多的选择/变量，无法使用公式。然而，我确实使用了你的建议:-
完成日期-决定不自动执行此操作，而是在未设置日期的情况下突出显示多个单元格的寿命。原因是我们需要在完成日期设置上进行一些尽职调查。”
我有3个细胞，需要捆绑在一起，并在同步一旦任务完成。我使用条件格式，并将其他2个单元格设置为红色，如果它们与更新的单元格不同步。这需要6个公式，我不得不把它们放在条件格式列表的顶部，但它工作得很好!
谢谢,
蒂姆
类别
It looks like you may need a COUNT\/DISTINCT\/COLLECT combo then.<\/p>
<\/p>
=COUNT(DISTINCT(COLLECT({Work Order}, {Book Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2023)))<\/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":106779,"type":"question","name":"Formula to collect the first and last values from children rows, but only if distinct.","excerpt":"Hello Community People, I'm working to create a report that will show a summary of the location where a person will be each day. The source sheet is set up with parent rows listing the date and child rows showing all the events scheduled for that date, and there is a single drop-down \"Location\" column to select the city in…","categoryID":322,"dateInserted":"2023-06-22T16:18:20+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T14:44:06+00:00","insertUserID":38776,"insertUser":{"userID":38776,"name":"Lindsay P.","url":"https:\/\/community.smartsheet.com\/profile\/Lindsay%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T14:44:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":38776,"lastUser":{"userID":38776,"name":"Lindsay P.","url":"https:\/\/community.smartsheet.com\/profile\/Lindsay%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T14:44:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":23,"score":null,"hot":3374984546,"url":"https:\/\/community.smartsheet.com\/discussion\/106779\/formula-to-collect-the-first-and-last-values-from-children-rows-but-only-if-distinct","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106779\/formula-to-collect-the-first-and-last-values-from-children-rows-but-only-if-distinct","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106779,"commentID":381987,"name":"Re: Formula to collect the first and last values from children rows, but only if distinct.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381987#Comment_381987","dateInserted":"2023-06-23T14:44:06+00:00","insertUserID":38776,"insertUser":{"userID":38776,"name":"Lindsay P.","url":"https:\/\/community.smartsheet.com\/profile\/Lindsay%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T14:44:11+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-23T14:44:08+00:00","dateAnswered":"2023-06-23T13:01:32+00:00","acceptedAnswers":[{"commentID":381931,"body":"
Try something like this:<\/p>
=IF(Level@row = 0, INDEX(CHILDREN(Location@row), 1) + IF(COUNT(DISTINCT(CHILDREN(Location@row))) > 1, \" to \" + INDEX(CHILDREN(Location@row), COUNT(CHILDREN(Location@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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106775,"type":"question","name":"Smartsheet Automation to Remove a Single Value from Multi-Select Cell","excerpt":"I have multiple columns that denote tasks; Task 1, Task 2, Task 3, Task 4. Each of these tasks can have a cell value of; Completed, In Progress, and Not Started. I then have a multi-value dropdown column called Team that denotes what teams are necessary to complete a project. I have an automation in place to add the teams…","categoryID":321,"dateInserted":"2023-06-22T15:27:34+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T14:08:08+00:00","insertUserID":162639,"insertUser":{"userID":162639,"name":"SimonJackson123","url":"https:\/\/community.smartsheet.com\/profile\/SimonJackson123","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AjtpHwf2uTI!-5Pot6_mIzI!FUQIrqeEh_H","dateLastActive":"2023-06-23T14:43:21+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-06-23T14:42:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":40,"score":null,"hot":3374983542,"url":"https:\/\/community.smartsheet.com\/discussion\/106775\/smartsheet-automation-to-remove-a-single-value-from-multi-select-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106775\/smartsheet-automation-to-remove-a-single-value-from-multi-select-cell","format":"Rich","tagIDs":[334],"lastPost":{"discussionID":106775,"commentID":381969,"name":"Re: Smartsheet Automation to Remove a Single Value from Multi-Select Cell","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381969#Comment_381969","dateInserted":"2023-06-23T14:08:08+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-06-23T14:42:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T14:43:42+00:00","dateAnswered":"2023-06-23T13:48:58+00:00","acceptedAnswers":[{"commentID":381955,"body":"
Ah. Ok. I had a very different idea of the data structure, so the screenshot definitely helps. Try this:<\/p>
=IF([Task 1]@row <> \"Completed\", \"Team 1\" + CHAR(10), \"\") + IF([Task 2]@row <> \"Completed\", \"Team 2\" + CHAR(10), \"\") + IF([Task 3]@row <> \"Completed\", \"Team 3\" + CHAR(10), \"\") + IF([Task 4]@row <> \"Completed\", \"Team 4\", \"\")<\/p>
<\/p>
The difference above as compared to typical IF statements is that usually they are nested, but in this case we want to \"add\" them together.<\/p>
<\/p>
NOTE: CHAR(10) is the built in delimiter for multi-select columns and will allow each team to be treated as an individual selection within the cell in the event you need to filter or report on it.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":334,"urlcode":"automations","name":"Automations"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
