尝试返回一次值，但当单元格值改变时不返回

KC琼斯

抱歉，我不知道该怎么说。基本上，在我们的项目中，我们从供应商那里得到一个报价，他们会自动把它输入到我们的项目表中。

因为这可以改变一个较低的价格或完全取消，我们设置了自动化，所以我们的价格栏更改为较低的成本，或为零，如果取消。但是，我注意到这意味着我们不能得到报价的全部信息。在我们的仪表板和报告中，我们有总成本和支出(成本值改变后)。

我试着把它设置成3个;报价、最终价格、支出成本。我尝试过使用SUMIF和其他各种公式。我也不知道有没有自动化的方法。我创建了一个报价成本列和一个我打算隐藏的助手列，因为我希望最终价格仍然显示在可见列中。我认为将这一栏改为报价成本会扰乱供应商的自动化操作(他们使用不同的程序自动进入我们的SS)。这也会让那些看了表格但不像我这样使用它的人感到困惑。

在尝试了各种公式后，我只得到了错误，直到我这样做:

=IF([总价]@行> 150，(SUM(0，[总价]@行)))

该公式确实返回总价值中的价格，但当总价列发生变化时，它仍然会发生变化。所以我需要一种方法让它只记录第一个输入的值。我已经确保我的专栏都被设置为货币，我使用了公式示例表和一些没有运气的人在这里问的问题。是否有办法在项目表或指标表中做到这一点?

她名叫Stara

嗨@KC琼斯

我希望你平安无事!

这可能会帮助!

请看看我下面的帖子与我开发的方法。

更多信息:

Hi all,<\/p>

I hope you're well and safe!<\/p>

I've developed a solution that lets you store the date or value.<\/strong><\/p>

You'd use the copy-row automation and a VLOOKUP or combination of INDEX\/MATCH to make it work.<\/p>

We'd trigger the copy-row to another sheet and get the created date\/other value and then use the VLOOKUP\/INDEX\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\/value that we can use, it will work.<\/p>

Also, this opens up more options with auto-numbering and similar.<\/p>

Depending on other workflows, you could reuse one sheet for multiple workflows.<\/p>

I hope that helps!<\/p>

Let me know if you have any questions!<\/p>

Stay safe and have a fantastic day!<\/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 help the Community by <\/em>marking it as helpful<\/em><\/strong>. It will make it easier for others to find a solution.<\/em><\/p>","bodyRaw":"[{\"insert\":\"Hi all,\\nI hope you're well and safe!\\nI've developed a solution that lets you \"},{\"attributes\":{\"bold\":true},\"insert\":\"store the date or value.\"},{\"insert\":\"\\n\\nYou'd use the copy-row automation and a VLOOKUP or combination of INDEX\\\/MATCH to make it work.\\nWe'd trigger the copy-row to another sheet and get the created date\\\/other value and then use the VLOOKUP\\\/INDEX\\\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\\\/value that we can use, it will work.\\n\\nAlso, this opens up more options with auto-numbering and similar.\\nDepending on other workflows, you could reuse one sheet for multiple workflows.\\n\\nI hope that helps!\\nLet me know if you have any questions!\\nStay safe and have a fantastic day!\\nBest,\\n\"},{\"attributes\":{\"bold\":true,\"link\":\"mailto:andree@workbold.com\"},\"insert\":\"Andrée Starå\"},{\"insert\":\" | Workflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/home\\\/leaving?allowTrusted=1&target=https%3A%2F%2Fworkbold.com%2F\"},\"insert\":\"WORK BOLD\"},{\"insert\":\"\\n✅\"},{\"attributes\":{\"italic\":true},\"insert\":\"Did my post(s) help or answer your question or solve your problem? Please help the Community by \"},{\"attributes\":{\"italic\":true,\"bold\":true},\"insert\":\"marking it as helpful\"},{\"attributes\":{\"italic\":true},\"insert\":\". It will make it easier for others to find a solution.\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-05-24T11:36:34+00:00","insertUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-06-13T20:56:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68589\/lock-or-store-date-value-solution-without-using-zapier","embedType":"quote","name":"Lock or Store Date\/Value Solution without using Zapier"}"> https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

工作/帮助吗?

我希望这对你有帮助!

注意安全，祝你度过美好的一周!

最好的

她名叫Stara|工作流顾问/ CEO @工作大胆

✅我的帖子是否帮助或回答了你的问题或解决了你的问题?请支持社区通过将其标记为有洞察力/投票支持或/和作为公认的答案．这将使别人更容易找到解决方案或帮助回答!

KC琼斯

这是我试过的另一个。因为我试图只收集更改前的信息，而这些列是它更改的原因(与自动化有关)，我认为我可以设置为只通过这些返回数字。

如果我用这个公式，它是成立的，但只有一个答案:

=IF([Site Decision]@row = "Yes"， (SUM(0，[总价]@row)))

但我认为我要么需要另一个函数，要么我的语法是错误的，当我试图添加第二个。

我也尝试过，但没有成功:

=IF(OR(COLLECT([工地决定]@row，“Yes”，([工地决定]@row，“Yes, Pending Waiver”=[总价]@row)

她名叫Stara

嗨@KC琼斯

我希望你平安无事!

这可能会帮助!

请看看我下面的帖子与我开发的方法。

更多信息:

Hi all,<\/p>

I hope you're well and safe!<\/p>

I've developed a solution that lets you store the date or value.<\/strong><\/p>

You'd use the copy-row automation and a VLOOKUP or combination of INDEX\/MATCH to make it work.<\/p>

We'd trigger the copy-row to another sheet and get the created date\/other value and then use the VLOOKUP\/INDEX\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\/value that we can use, it will work.<\/p>

Also, this opens up more options with auto-numbering and similar.<\/p>

Depending on other workflows, you could reuse one sheet for multiple workflows.<\/p>

I hope that helps!<\/p>

Let me know if you have any questions!<\/p>

Stay safe and have a fantastic day!<\/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 help the Community by <\/em>marking it as helpful<\/em><\/strong>. It will make it easier for others to find a solution.<\/em><\/p>","bodyRaw":"[{\"insert\":\"Hi all,\\nI hope you're well and safe!\\nI've developed a solution that lets you \"},{\"attributes\":{\"bold\":true},\"insert\":\"store the date or value.\"},{\"insert\":\"\\n\\nYou'd use the copy-row automation and a VLOOKUP or combination of INDEX\\\/MATCH to make it work.\\nWe'd trigger the copy-row to another sheet and get the created date\\\/other value and then use the VLOOKUP\\\/INDEX\\\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\\\/value that we can use, it will work.\\n\\nAlso, this opens up more options with auto-numbering and similar.\\nDepending on other workflows, you could reuse one sheet for multiple workflows.\\n\\nI hope that helps!\\nLet me know if you have any questions!\\nStay safe and have a fantastic day!\\nBest,\\n\"},{\"attributes\":{\"bold\":true,\"link\":\"mailto:andree@workbold.com\"},\"insert\":\"Andrée Starå\"},{\"insert\":\" | Workflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/home\\\/leaving?allowTrusted=1&target=https%3A%2F%2Fworkbold.com%2F\"},\"insert\":\"WORK BOLD\"},{\"insert\":\"\\n✅\"},{\"attributes\":{\"italic\":true},\"insert\":\"Did my post(s) help or answer your question or solve your problem? Please help the Community by \"},{\"attributes\":{\"italic\":true,\"bold\":true},\"insert\":\"marking it as helpful\"},{\"attributes\":{\"italic\":true},\"insert\":\". It will make it easier for others to find a solution.\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-05-24T11:36:34+00:00","insertUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-06-13T20:56:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68589\/lock-or-store-date-value-solution-without-using-zapier","embedType":"quote","name":"Lock or Store Date\/Value Solution without using Zapier"}"> https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

工作/帮助吗?

我希望这对你有帮助!

注意安全，祝你度过美好的一周!

最好的

她名叫Stara|工作流顾问/ CEO @工作大胆

✅我的帖子是否帮助或回答了你的问题或解决了你的问题?请支持社区通过将其标记为有洞察力/投票支持或/和作为公认的答案．这将使别人更容易找到解决方案或帮助回答!

KC琼斯

@Andree Stara

我不知道VLOOKUP是否会工作，但我认为INDEX/MATCH会。我只是不知道该怎么写公式。每一行都有一个唯一的号码，因此我应该能够做到这一点。例:

BU列是800100(为保密而设的假号码)，供应商在初步检查后输入$1000.00总价。BU进入PO，供应商获得NTP。然后，可能客户取消了工作，所以我将状态更改为取消，自动化将总价格更改为0.00美元。或供应商去现场完成工作，但由于各种因素不能。因为他们派来的人必须付费，我们就支付较低的金额来支付费用。所以我改变了状态，它会自动把总价改成150美元

我已经设置了自动复制行到另一个工作表，但我必须手动复制每一个现有的行。我要怎么从这里开始?

KC琼斯

@Andree Stara

我可以在网上找到一个例子，我终于得到了它工作!非常感谢!

对于未来偶然发现这一点的人，这是我的最后一个公式。我使用了复制行自动化并创建了一个助手表。然后我只索引我需要的数据，并通过一个代码来匹配它，这是每一行的唯一ID:

=INDEX({报价成本助手指标范围1}，MATCH([工作#]@行，{报价成本助手指标范围3}，0))

*范围1是成本，范围3是与项目表相匹配的工作#。

对于不符合条件的行，我确实得到了No Match错误，将其拉到我的助手表中。然而，我计划隐藏的列，因为我使用它的报告，因为我不能求和的列，因为错误。所以我在我的项目表中创建了一个助手列，并使用这个公式将错误转换为数值:

(引用成本)= IFERROR(价值(@row), 0)

然后，我就可以使用helper列上的sum公式来计算所引用的成本。