探索。问。的答案。

或 探索讨论

奇怪的行为公式

maraoremaraore ✭
编辑04/28/22 在公式和函数
04/28/22 编辑04/28/22
已回答-有待检讨

问候,

我正在开发一个解决方案，从生成的系统日期/时间自动捕获24小时时间(作为一个整数值)。我们的目标是允许用户在平板电脑上使用表单，在三个表单中输入一些信息字段。我的解决方案是正确地转换每个表格中的时间:

correct_times.png

以上是我的测试数据的一部分。黄色突出显示的第一列是自动系统日期/时间的转换(灰色)。第二和第三列黄色的是来自其他表格的引用，这些表格使用相同的公式来计算24小时转换。

我的问题来自于试图用表单更新表格。当一个新行被提交时，公式的行为会很奇怪。

error1.png

上面是与以前相同的数据，但是提交了一个新的记录。现在，公式计算对一些人有效，但添加一个逗号，或返回一个错误。

更奇怪的是，当我打开工作表并保存时，数据返回到预期值。

我要补充的是，我的转换公式很复杂(大约518个字符)，有多个链式函数。有什么想法，为什么我需要做这个“手动刷新”公式的工作?


谢谢你！

标签:

答案

  • 马特·C。马特·C。 ✭✭

    当你创建另一个表格与#无效单元格引用之前，你用保存方法修复它会发生什么?这真的是一个破碎的公式还是只是一个显示问题?

  • 詹姆斯•马詹姆斯•马 ✭✭✭

    我也有一个像这样的。一个相当复杂的预算表，在编辑和保存时显示错误，但在刷新时就会解决。每次我遇到它都让我紧张。

    所以，没有答案给你，但更多的是说，这不是你，这是Smartsheet。

    也就是说，可能有一些改进可以清理它，或者也许我可以找出这个超级复杂的公式的哪一部分是导致问题的原因，但我只是没有动机。

    我的系统是由一堆helper列组成的，我没有将它们分解到一个单元格中，我仍然无法理解。

    我的朋友经常说#受阻

    我认为这与我看到的另一个问题有关——非常复杂的IF语句，其中有一个非常简单的T/F测试，比如，如果单元格中有东西，那么MESSAGE to CLEAR THE cell。没有其他条件可以产生这样的信息。我清空了手机，信息还在。保存。消息还在那里。刷新。消息消失。

  • maraoremaraore ✭
    What happens when you create another sheet with a cell reference to the #INVALID cells before you fix it with the save method? Is it really a broken formula or just a display issue?<\/p>","bodyRaw":"[{\"insert\":\"What happens when you create another sheet with a cell reference to the #INVALID cells before you fix it with the save method? Is it really a broken formula or just a display issue?\\n\"}]","format":"rich","dateInserted":"2022-04-28T23:56:22+00:00","insertUser":{"userID":145151,"name":"Matt C.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matt%20C.","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJz4VcTCUqqOcHQ9fFrcx-PshvGelJbleisHSXRd=s96-c","dateLastActive":"2022-05-05T00:19:04+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\/327816#Comment_327816","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327816#Comment_327816

    马特,

    在我的第一个截图中的主表确实做了一个单元格引用，并提取其引用表中的值:

    reference.png
    I have one like this too. A pretty complicated budget sheet that shows errors while editing, and after saving, but which resolves when I refresh. It makes me nervous every time I encounter it.<\/p>

    So, no answer for you, but more just saying that it's not you, it's Smartsheet.<\/p>

    That said, there might be some refinement that can clean it up, or maybe I can chase down which part of the super complex formula is causing the issue, but I just haven't had the motivation.<\/p>

    My system is made up of a bunch of helper columns that I have not collapsed into one cell, and I still can't figure it out.<\/p>

    Mine often say #BLOCKED<\/p>

    I think it's connected to another problem that I have seen - really complex IF statements which have one really simple T\/F test in them, like, if something is in this cell, then MESSAGE TO CLEAR THE CELL. And there is no other condition that creates that message. I clear the cell, message is still there. Save. Message still there. Refresh. Message goes away.<\/p>","bodyRaw":"[{\"insert\":\"I have one like this too. A pretty complicated budget sheet that shows errors while editing, and after saving, but which resolves when I refresh. It makes me nervous every time I encounter it.\\nSo, no answer for you, but more just saying that it's not you, it's Smartsheet.\\nThat said, there might be some refinement that can clean it up, or maybe I can chase down which part of the super complex formula is causing the issue, but I just haven't had the motivation.\\nMy system is made up of a bunch of helper columns that I have not collapsed into one cell, and I still can't figure it out.\\nMine often say #BLOCKED\\nI think it's connected to another problem that I have seen - really complex IF statements which have one really simple T\\\/F test in them, like, if something is in this cell, then MESSAGE TO CLEAR THE CELL. And there is no other condition that creates that message. I clear the cell, message is still there. Save. Message still there. Refresh. Message goes away.\\n\"}]","format":"rich","dateInserted":"2022-04-29T00:00:46+00:00","insertUser":{"userID":127181,"name":"James Keuning","title":"","url":"https:\/\/community.smartsheet.com\/profile\/James%20Keuning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-05-02T20:31:59+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\/327818#Comment_327818","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327818#Comment_327818

    詹姆斯,

    在您看来，我的公式也是复杂的嵌套if语句，它是根据helper列(除去日期的系统日期/时间)构建的。下面我把它分成了更容易读懂的格式:

    =替代((如果发现(“我”,(电子邮件保护)) > 0, if (find ("12"，(电子邮件保护)) <> 0，替换(替换(替换)(电子邮件保护)、“我”、“”),“点”,“”),“12”,“00”),替代(替代((电子邮件保护)， " am "， "")， " pm "， ""))， if (find ("12"，(电子邮件保护)) <> 0，替换(替换()(电子邮件保护)、“我”、“”),“点”,“”),替代(替代(替代((电子邮件保护)，" am "， "")，" pm "， "")，左((电子邮件保护)找到(”:“(电子邮件保护)) - 1)，值(左((电子邮件保护)找到(”:“(电子邮件保护)) - 1)) + 12, 1))))，“:”，“”)

    其他更简单的公式可以轻松地与新条目进行转换和调整——这是我在其他项目中依赖的，在用户输入数据时触发事件。这是我用复杂度和长度得出的第一个公式。

    不确定我如何可以优化这个逻辑-我想到了省去“AM/PM”字符，使公式更小，但我需要他们的24小时逻辑(除非我使用条件帮助列?)

  • maraoremaraore ✭

    我很抱歉，我只是注意到我在第一篇和之前的文章中的截图显示了错误的结果。

    纠正times.png

    这是带有正确值的实际最终结果。不更改时间公式或其引用的工作表，只刷新和保存以重新计算所有工作表上的值。

  • Sameer KarkhanisSameer Karkhanis ✭✭✭✭✭

    我不确定你在其他表格中的公式是什么，但我只是从你的截图中得到了一个大致的观察和可能的提示:

    如果您注意到所有的错误都发生在日期/时间值中的时间是单个数字小时的那些行。例如，04/28/22 2:28 PM而不是04/28/22 02:28 PM。如果小时部分是单个数字，检查你的转换公式是否有错误。

登录或注册置评。