工作流自动化-分配人员功能。
我为我的团队创建了轮班制。左轴显示日期和日期，上轴显示按团队划分的每个人。在表格中，每个人每天都被分配一个字母，这取决于他们被分配到的班次(例如D =白班，a =下午班)。
然后，我设置了一个自动化系统，每天收集每个轮班类型的每个人。
自动化操作如下。
根据他们当天分配的字母，这将馈送到另一个表中，该表收集每个班次的不同人员。
当你重新分配某人的班次时，自动化就会起作用。例如，你在轮班表上将我从D(白班)分配到N(夜班)，然后自动化将我的联系信息放入右侧表的夜班列中。然而，我的名字仍然留在白班一栏。
有没有办法从它所在的前一个单元格中删除名称?
我理解使用and index(collect)或Join(collect)可能更好，但我还没有找到一种方法来做到这一点，因为它必须查找许多不同的变量。
提前感谢!
最佳答案
-
杰夫瑞斯曼 ✭✭✭✭✭✭当我创建一个类似的公式时，如果IF都不为真(其中没有列有“D”)，则单元格中放置“0”。这是意想不到的!
但是，不像我在你身上看到的，当至少一个if变为真时，0就消失了。
当我向其中一个if添加负条件“”(一个空白值)时，当所有条件都不为真时，我不再得到“0”。所以在你的第一个IF的末尾加上这个(如下所示)，看看是否可以去掉0。
=如果((电子邮件保护)= "D"， $Employee$1”、“）
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
答案
-
杰夫瑞斯曼 ✭✭✭✭✭✭
代替自动化，您可以使用在每个移位的列中添加一堆IF语句来完成此操作。然而，你需要这样做将“Shift”列从“联系人”类型列转换为常规的“多选择”列，你需要这样做创建一个新的常规多选择列(我们称之为“Employee”)，列出你的员工:
让我们从白班栏目开始:
=如果((电子邮件保护)= "D"， Employee1) + IF((电子邮件保护)= "D"， Employee2) + IF((电子邮件保护)= "D"， Employee3) + IF((电子邮件保护)= "D"， Employee4) + IF((电子邮件保护)= "D"， Employee5) + IF((电子邮件保护)= "D"， Employee6) + IF((电子邮件保护)= "D"， Employee7) + IF((电子邮件保护)= "D"， Employee8) + IF((电子邮件保护)= "D"， Employee9)
这样做的结果是，在多选择列中，每个名称在多选择单元格中作为不同的值列出。
对于Small Day Shift列，只需将上述公式中的“D”更改为“SD”，以此类推。
这样，当某人的轮班发生变化时，公式将更新，员工的姓名将移动到正确的轮班列中。
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
-
JPFORDTE ✭
嗨,杰夫,
非常感谢!这个作品。
一个小问题:
你知道为什么有些行返回0吗?不知道为什么。
-
杰夫瑞斯曼 ✭✭✭✭✭✭
在没有看到您的完整公式和员工姓名查找表之前，我不能说。
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
-
JPFORDTE ✭
雇员列表:
公式(这一个查找正确的人，但也有一个零，在这个单元格返回):
=如果((电子邮件保护)= "D"， $Employee$1) + IF((电子邮件保护)= "D"， $Employee$2) + IF((电子邮件保护)= "D"， $Employee$3) + IF((电子邮件保护)= "D"， $Employee$4) + IF((电子邮件保护)= "D"， $Employee$5) + IF((电子邮件保护)= "D"， $Employee$6) + IF((电子邮件保护)= "D"， $Employee$7) + IF((电子邮件保护)= "D"， $Employee$8) + IF((电子邮件保护)= "D"， $Employee$9) + IF((电子邮件保护)= "D"， $Employee$10) + IF((电子邮件保护)= "D"， $Employee$11) + IF((电子邮件保护)= "D"， $Employee$12) + IF((电子邮件保护)= "D"， $Employee$13) + IF((电子邮件保护)= "D"， $Employee$14) + IF((电子邮件保护)= "D"， $Employee$15) + IF((电子邮件保护)= "D"， $Employee$16) + IF((电子邮件保护)= "D"， $Employee$17) + IF((电子邮件保护)= "D"， $Employee$18)
-
杰夫瑞斯曼 ✭✭✭✭✭✭
当我创建一个类似的公式时，如果IF都不为真(其中没有列有“D”)，则单元格中放置“0”。这是意想不到的!
但是，不像我在你身上看到的，当至少一个if变为真时，0就消失了。
当我向其中一个if添加负条件“”(一个空白值)时，当所有条件都不为真时，我不再得到“0”。所以在你的第一个IF的末尾加上这个(如下所示)，看看是否可以去掉0。
=如果((电子邮件保护)= "D"， $Employee$1”、“）
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
-
JPFORDTE ✭
传说!谢谢你杰夫，这个成功了!
-
杰夫瑞斯曼 ✭✭✭✭✭✭
太棒了!
现在我正在考虑它-这就是为什么0被放置在单元格中，以及为什么添加“”修复它:
因为我们没有给if一个负条件(如果逻辑表达式为假的值)，并且我们将公式一起添加，所以Smartsheet假设负条件是默认的负数值，即0。(例如，在复选框字段中，1为正数，0为负数，未选中。)如果您添加一串If，其条件是数字(不在引号中)，Smartsheet将以数学方式将值添加在一起。通过添加“”(空白文本)否定条件，这意味着它不是将公式视为0 + 0 + 0等，而是将其视为空白文本+ 0 + 0，有效地将单元格转换为文本单元格，因此不会将所有零“添加”在一起。
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
类别
Hi @mgreenwalt<\/a> <\/p> I hope you're well and safe!<\/p> Here's a possible workaround or workarounds <\/strong><\/p> Would any of those options work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/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":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":446,"urlcode":"update-requests","name":"Update Requests"}]},{"discussionID":106397,"type":"question","name":"Line Item Signature Requests","excerpt":"I have a database of assets, and want to send each asset owner an automated form, which I can do, with their asset listing. What I need to receive is a \"signature\" that the assets are acknowledged. by the owner, and include verbiage that the asset owner agrees to abide by. I can create the form, automate it, but I have not…","categoryID":321,"dateInserted":"2023-06-13T20:39:57+00:00","dateUpdated":null,"dateLastComment":"2023-06-14T14:00:24+00:00","insertUserID":162344,"insertUser":{"userID":162344,"name":"Pauline J","title":"Director of Operations\/IT","url":"https:\/\/community.smartsheet.com\/profile\/Pauline%20J","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-14T16:53:43+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-06-15T10:24:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":32,"score":null,"hot":3373443021,"url":"https:\/\/community.smartsheet.com\/discussion\/106397\/line-item-signature-requests","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106397\/line-item-signature-requests","format":"Rich","lastPost":{"discussionID":106397,"commentID":380488,"name":"Re: Line Item Signature Requests","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380488#Comment_380488","dateInserted":"2023-06-14T14:00: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-06-15T10:24:15+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-14T13:05:40+00:00","dateAnswered":"2023-06-13T22:18:29+00:00","acceptedAnswers":[{"commentID":380401,"body":" Hi @Pauline J<\/a> <\/p> I hope you're well and safe!<\/p> Here's a possible workaround or workarounds <\/strong><\/p> Would any of those options work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/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>"},{"commentID":380454,"body":" @Pauline J<\/a> <\/p> Excellent!<\/p> Happy to help!<\/p> Here's an example of how it could look.<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/WLCSO8N85MRQ\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![https:\/\/us.v-cdn.net\/6031209\/uploads\/89KTL3S08TS0\/image.png<\/a><\/p>Would you happen to know why for some rows it returns a zero? Couldn't figure out why.<\/p>","bodyRaw":"[{\"insert\":\"Hi Jeff,\\nthank you very much! this works. \\none little issue:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/89KTL3S08TS0\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/89KTL3S08TS0\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"Would you happen to know why for some rows it returns a zero? Couldn't figure out why.\\n\\n\\n\"}]","format":"rich","dateInserted":"2022-04-25T09:36:09+00:00","insertUser":{"userID":140218,"name":"JPFORDTE","url":"https:\/\/community.smartsheet.com\/profile\/JPFORDTE","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7Zlpu6ODVaM!XIEqH7eWCvs!Ehtq2n17psd","dateLastActive":"2022-04-28T08:05:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327201#Comment_327201","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327201#Comment_327201](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/89KTL3S08TS0\/image.png\"){kind=link}
