奇异公式行为

maraore
maraore ✭
编辑04/28/22 在公式和函数

问候,

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

correct_times.png

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

我的问题来自于试图用表单更新工作表。当提交新行时，公式的行为很奇怪。

error1.png

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

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

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


谢谢你！

标签:

答案

  • 迈克电视
    迈克电视 ✭✭✭✭✭

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

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

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

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

    也就是说，可能会有一些改进可以清理它，或者也许我可以追踪超级复杂公式的哪一部分导致问题，但我只是没有动力。

    我的系统是由一堆辅助列组成的，我还没有将它们折叠成一个单元格，但我仍然无法弄清楚。

    我的经常写#BLOCKED

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

  • maraore
    maraore ✭
    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":"Mike TV","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20TV","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJz4VcTCUqqOcHQ9fFrcx-PshvGelJbleisHSXRd=s96-c","dateLastActive":"2022-06-22T00:26:05+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-06-16T19:25:15+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

    詹姆斯,

    根据您的观点，我的公式也是基于helper列(去掉日期的系统日期/时间)构建的复杂嵌套if语句。下面我把它分成了一种更易读的格式:

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

    另外，更简单的公式可以很容易地转换和调整新条目——当用户输入数据时，我在其他项目中依赖它来触发事件。这是我做的关于复杂度和长度的第一个公式。

    我不知道如何优化这个逻辑——我想去掉“AM/PM”字符，使公式更小，但我需要它们用于24小时逻辑(除非我使用条件助手列?)。

  • maraore
    maraore ✭

    我的道歉，我只是注意到我的截图在第一个和之前的帖子呈现不正确的结果。

    纠正times.png

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

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

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

    如果您注意到，所有错误都是针对日期/时间值中的时间为个位数小时的那些行。例如，04/28/22 2:28 PM而不是04/28/22 02:28 PM。如果小时部分是个位数，请检查转换公式是否出错。

类别