在表格中组织数据

大家好!

我正在做一个项目,我需要安排员工在不同的日期参加各种会议。我已经建立了一个表格并收集了这些数据。我列出了每个日期作为一个问题,所以我有10个表单字段,日期作为列标题(星期四,3月2日)和工作人员选择可用或不可用。

现在我有了这些数据,我意识到如果不手动排序,我不知道如何找出每个日期谁是可用的。我最初没有设置我的表单与表单字段与多选,因为我担心数据被打乱,但现在我想知道如果我采取了错误的方法。确定哪些会话分配给哪些可用员工的过程变得非常手工,我想知道这里是否有人做过类似的项目并有任何建议。乐意截图我的数据,如果这是有帮助的。

从本质上讲,我想根据员工的可用性将他们分配到不同的日期,但每个日期的员工数量是有限制的。什么好主意吗?

标签:

最佳答案

  • 杰夫瑞斯曼
    杰夫瑞斯曼 ✭✭✭✭✭✭
    ✓回答

    @ABoado我有个想法:

    我假设您的工作表是一个网格,其中一列是员工姓名,然后是每个日期的列,每个日期列中列出了每个员工姓名的可用或不可用,对吗?

    复制一份工作表,然后删除除员工姓名外的所有数据,但不要删除列。我把它命名为sheet2。

    从第一个日期列的最上面一行开始。如果单元格等于“Available”,使用INDEX/COLLECT从主表中拉入人员名称,创建对主表的引用:

    =INDEX(COLLECT({主表名称列},{主表名称列},[员工名称]@row,{主表3月2日列},"Available"), 1)

    这里的逻辑是这样的:给我主表人员名称列中的值,其中人员名称与本行中的人员名称相同,并且3月2日列等于“Available”。

    把公式抄到表格2的3月2日那一栏。

    将公式复制到下一个日期列中,然后将主表中的日期列范围替换为下一个日期列。将公式复制到表格2的日期栏,以此类推。

    当你完成后,你会有一个表格,每个日期列都包含了那天可用的名字。如果需要,可以在每个列的底部将这些值连接到一个单元格中。

    问候,

    杰夫瑞斯曼IT业务分析师及项目协调员,三菱电机特灵美国

    链接:智能表功能帮助页面链接:Smartsheet公式错误信息

    如果我的回答帮助解决了您的问题,请将其标记为接受,以便其他用户稍后可以找到它。谢谢!

答案

  • 杰夫瑞斯曼
    杰夫瑞斯曼 ✭✭✭✭✭✭
    ✓回答

    @ABoado我有个想法:

    我假设您的工作表是一个网格,其中一列是员工姓名,然后是每个日期的列,每个日期列中列出了每个员工姓名的可用或不可用,对吗?

    复制一份工作表,然后删除除员工姓名外的所有数据,但不要删除列。我把它命名为sheet2。

    从第一个日期列的最上面一行开始。如果单元格等于“Available”,使用INDEX/COLLECT从主表中拉入人员名称,创建对主表的引用:

    =INDEX(COLLECT({主表名称列},{主表名称列},[员工名称]@row,{主表3月2日列},"Available"), 1)

    这里的逻辑是这样的:给我主表人员名称列中的值,其中人员名称与本行中的人员名称相同,并且3月2日列等于“Available”。

    把公式抄到表格2的3月2日那一栏。

    将公式复制到下一个日期列中,然后将主表中的日期列范围替换为下一个日期列。将公式复制到表格2的日期栏,以此类推。

    当你完成后,你会有一个表格,每个日期列都包含了那天可用的名字。如果需要,可以在每个列的底部将这些值连接到一个单元格中。

    问候,

    杰夫瑞斯曼IT业务分析师及项目协调员,三菱电机特灵美国

    链接:智能表功能帮助页面链接:Smartsheet公式错误信息

    如果我的回答帮助解决了您的问题,请将其标记为接受,以便其他用户稍后可以找到它。谢谢!

  • ABoado
    ABoado ✭✭✭

    非常感谢你,杰夫!!你的主意太棒了!我非常感激你的帮助!!

You would need to use a Move Row or Copy Row automation.<\/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":474,"urlcode":"data-shuttle","name":"Data Shuttle"}]},{"discussionID":106742,"type":"question","name":"Conditional Formatting","excerpt":"Hi, I need to apply conditional formatting based on another row in the sheet. Higher it should be highlighted in red, equal to or below highlighted in green. It would be easy to just use the target figures in the conditional formatting rules but I need to be able to change it frequently without changing all the formatting…","categoryID":321,"dateInserted":"2023-06-21T22:41:30+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T15:15:27+00:00","insertUserID":160222,"insertUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":160222,"lastUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":36,"score":null,"hot":3374836617,"url":"https:\/\/community.smartsheet.com\/discussion\/106742\/conditional-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106742\/conditional-formatting","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":106742,"commentID":381752,"name":"Re: Conditional Formatting","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381752#Comment_381752","dateInserted":"2023-06-22T15:15:27+00:00","insertUserID":160222,"insertUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ARIIND3W8G3C\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T15:15:35+00:00","dateAnswered":"2023-06-22T03:52:17+00:00","acceptedAnswers":[{"commentID":381675,"body":"

@isabonita<\/a> is on the right track. This is kind of an involved answer, but once you figure out the logic, it isn't that bad.<\/p>

You cannot reference specific cells in the conditional format in a manner that would make this easy. You cannot, for instance, say IF this specific cell is greater than that cell, apply this format. You can do it for the same row, but not always referencing the same cell. We get around this by inserting a \"helper\" column for each condition we want to check. The checkbox format works great. In the conditional format rule, you can simply say IF THIS cell is checked, apply the format to THAT cell. <\/p>

To set the checkbox to checked\/unchecked, we use this column formula: =IF(PDP@row > PDP#, 1, 0)<\/p>

But what is PDP#? This is another issue we have to work around. You cannot reference the first cell of a column in a column formula, you can only reference cells in the same row. To work around this, we add fields into the sheet summary. [PDP]# references the PDP field in the sheet summary. In order to make updating these values easier, we set the value of this field equal to the first cell in the PDP column using =PDP1<\/p>

After creating all of the helper columns, setting up the column formulas for each to determine which ones are checked, creating all of the necessary fields in the sheet summary, and setting those equal to the first cells in each of the respective columns within the sheet, we can set up the conditional formatting.<\/p>

Check out the image below to get an idea of how to setup the formats. Unfortunately, we have one more workaround to setup at this stage. Since the formatting will apply to every row in the sheet, it will apply to the first row as well and all of those cells will be green. To get around this, add the first format pictured below as the first one in your list. For the background color, you must select \"white\" in order to avoid the highlight. If you set it to \"no background\", you will get an error because Smartsheet does not see \"no background\" as a format.<\/p>

After everything is setup and running, you should be able to update the cells in your target row and things will work themselves out automatically.<\/p>


<\/p>


<\/p>

\n
\n \n \"Condition.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

I hope this helps, and I really hope it makes sense.<\/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":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]},{"discussionID":106711,"type":"question","name":"Is there a glitch in the alert automation when trying to add the double curly brackets?","excerpt":"When creating an alert based on a row being added or changed, I can set the alert to go out hourly, daily, weekly. However, when I add the fields with the double curly brackets, it will not allow me to use the hourly, daily, weekly feature. Is this a glitch in Smartsheet? Has anyone else noticed this?","categoryID":321,"dateInserted":"2023-06-21T17:08:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T16:21:48+00:00","insertUserID":158955,"insertUser":{"userID":158955,"name":"Andrea_Thompson","url":"https:\/\/community.smartsheet.com\/profile\/Andrea_Thompson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmEthyIFMno!3R8u_8p4Dvo!yYe8n_1IW84","dateLastActive":"2023-06-22T16:22:37+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-22T19:34:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":49,"score":null,"hot":3374821224,"url":"https:\/\/community.smartsheet.com\/discussion\/106711\/is-there-a-glitch-in-the-alert-automation-when-trying-to-add-the-double-curly-brackets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106711\/is-there-a-glitch-in-the-alert-automation-when-trying-to-add-the-double-curly-brackets","format":"Rich","lastPost":{"discussionID":106711,"commentID":381771,"name":"Re: Is there a glitch in the alert automation when trying to add the double curly brackets?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381771#Comment_381771","dateInserted":"2023-06-22T16:21:48+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-22T19:34:53+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-22T17:28:51+00:00","dateAnswered":"2023-06-21T19:34:19+00:00","acceptedAnswers":[{"commentID":381597,"body":"

Sorry. I misspoke. You are correct that it is not a time based trigger, but it is a time based sending of the alert. If you have 10 rows that changed within that 1 hour period, then it will send all 10 of those rows within the same alert. Since it is pulling 10 rows, it does not know which row to pull the {{Placeholder}} from.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=341&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":5398,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

使用Smartsheet的趋势