报告时间表上的变化。

TCJ
TCJ ✭
在公式和函数

你好. .我有多个甘特表来跟踪几个项目里程碑，以及与这些里程碑相关的客户付款。我的财务团队想要一份月度报告，说明由于项目延误而导致的里程碑付款的变化，以便预测收入。

我的想法是建立一个工作流程，在每个月的最后一天运行。它将把所有已更改的行移动到新工作表中。这将允许我保持这些表的运行总数见下面的例子。有两个不同时间的数据(参见modified列—在本例中以2分钟为单位变化，而不是一个月)。黄色突出显示的是第一个时间点，蓝色突出显示的是第二个时间点，红色完成日期已更改。

image.png

我需要报告里程碑付款的延迟。例如，在这个例子中，里程碑2从11/30/22推迟到2/1/23。这需要特别标记。下个月我需要重复这一点。

我尝试使用基线方差函数，它显示了延迟，但它不允许从一个月到另一个月的比较来突出这些变化。我需要能够跨多个表执行此操作，并希望将其滚动到单个报告中。我希望这是有意义的。

最佳答案

  • 詹姆斯•马
    詹姆斯•马 ✭✭✭✭
    ✓回答


    我最近发布了一个执行此功能的样本表。基本上，您正在询问Smartsheet:“查看(1)匹配此PRIMARY列的每条记录，以及(2)具有MODIFIED小于此MODIFIED的每条记录，并给我MAX记录。”然后使用INDEX和MATCH来计算增量。

    看一看:

    https://app.smartsheet.com/b/publish?EQBCT=0d07d37f2d2a4333ae2a210ccb6284f3

    在本例中，最终答案字段中的公式是您想要结束的地方，但不是IF语句，而是计算差值。

    如果你用你的数据发布一个表格，并给我链接，我可以为你建立它。

答案

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

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

You will need to substitute each space with %20 so when you are adding the Primary Column value use,<\/p>
=SUBSTITUTE([Primary Column]@row, \" \", \"%20\")\n<\/pre>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-03 11:16:07","updateUser":{"userID":152031,"name":"austinov","url":"https:\/\/community.smartsheet.com\/profile\/austinov","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-03T11:16:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":95022,"type":"question","name":"Determine if form submissions are missing","excerpt":"Hi there. We have 16 geographically dispersed service locations who perform time of service collections and bank deposits. Our finance team needs to capture and reconcile these collections & deposits on a daily basis. I have created a form to collect time of service collection information from each location, and I'm trying…","categoryID":322,"dateInserted":"2022-09-02T17:59:05+00:00","dateUpdated":null,"dateLastComment":"2022-09-02T20:41:55+00:00","insertUserID":99446,"insertUser":{"userID":99446,"name":"JS_NCHC","url":"https:\/\/community.smartsheet.com\/profile\/JS_NCHC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T22:15:46+00:00","banned":0,"punished":0,"private":false,"label":""},"updateUserID":null,"lastUserID":99446,"lastUser":{"userID":99446,"name":"JS_NCHC","url":"https:\/\/community.smartsheet.com\/profile\/JS_NCHC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T22:15:46+00:00","banned":0,"punished":0,"private":false,"label":""},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3324293460,"url":"https:\/\/community.smartsheet.com\/discussion\/95022\/determine-if-form-submissions-are-missing","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95022\/determine-if-form-submissions-are-missing","format":"Rich","lastPost":{"discussionID":95022,"commentID":342319,"name":"Re: Determine if form submissions are missing","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/342319#Comment_342319","dateInserted":"2022-09-02T20:41:55+00:00","insertUserID":99446,"insertUser":{"userID":99446,"name":"JS_NCHC","url":"https:\/\/community.smartsheet.com\/profile\/JS_NCHC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T22:15:46+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":"2022-09-02T20:46:38+00:00","dateAnswered":"2022-09-02T20:41:55+00:00","acceptedAnswers":[{"commentID":342319,"body":"
Hi again.  I figured out the core formula I needed, so I thought I'd update my post in case others have similar questions in the future.<\/p>
Form for submission is part of \"Daily Collections\" sheet, and SITE NAME is the site submitting the form.  I created an Audit sheet with a list of SITE NAME's, and look to the Daily Collections sheet to determine if at least one submission matches with SITE NAME, resulting in \"Y\" or \"N\".<\/p>
=IF(COUNTIF({Daily Collections SITE NAME}, =SITE NAME@row) > 0, \"Y\", \"N\")<\/p>
Thanks! 😁<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-02 20:46:38","updateUser":{"userID":99446,"name":"JS_NCHC","url":"https:\/\/community.smartsheet.com\/profile\/JS_NCHC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T22:15:46+00:00","banned":0,"punished":0,"private":false,"label":""}}},"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":95016,"type":"question","name":"Countifs with multiple sheet references and CONTAINS","excerpt":"Hi! I am struggling with how to build the correct formula to provide the result of the following scenario: I am using the Smartsheet \"Fruit Sheet\" to create a formula in the Smartsheet \"Orchard\" Search \"Fruit Sheet\" for any \"Apples\" that may be \"Red\" in the \"Summer\" The sheet has several fruits and several apples. The…","categoryID":322,"dateInserted":"2022-09-02T14:58:09+00:00","dateUpdated":"2022-09-02T15:08:46+00:00","dateLastComment":"2022-09-02T15:55:50+00:00","insertUserID":152024,"insertUser":{"userID":152024,"name":"MoyaC","url":"https:\/\/community.smartsheet.com\/profile\/MoyaC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T15:56:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":152024,"lastUser":{"userID":152024,"name":"MoyaC","url":"https:\/\/community.smartsheet.com\/profile\/MoyaC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T15:56:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3324266039,"url":"https:\/\/community.smartsheet.com\/discussion\/95016\/countifs-with-multiple-sheet-references-and-contains","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95016\/countifs-with-multiple-sheet-references-and-contains","format":"Rich","lastPost":{"discussionID":95016,"commentID":342299,"name":"Re: Countifs with multiple sheet references and CONTAINS","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/342299#Comment_342299","dateInserted":"2022-09-02T15:55:50+00:00","insertUserID":152024,"insertUser":{"userID":152024,"name":"MoyaC","url":"https:\/\/community.smartsheet.com\/profile\/MoyaC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T15:56:00+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":"2022-09-02T15:55:57+00:00","dateAnswered":"2022-09-02T15:46:55+00:00","acceptedAnswers":[{"commentID":342297,"body":"
@MoyaC<\/a> <\/p>
The below should work for you:<\/p>
=COUNTIFS({FRUIT_SHEET_FruitType}, \"Apple\", {FRUIT_SHEET_Color}, has(@cell,\"Red\")<\/strong>, {FRUIT_SHEET_Season}, \"Summer\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-02 15:55:57","updateUser":{"userID":152024,"name":"MoyaC","url":"https:\/\/community.smartsheet.com\/profile\/MoyaC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T15:56:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}">