上周价值
你好,
有没有人知道一个公式，可以从一个现有的列从使用细胞历史或活动日志前一周拉值?
例如，我可以在单元格历史记录中看到上周的值，但我想通过列自动拉出它，以创建上周与本周的视图。我的工作表不是基于日期的，所以我不能简单地使用公式来查找先前日期的值。
谢谢你！
Cierra
答案
-
迈克Wilday ✭✭✭✭✭✭单元格历史记录和活动数据在智能工作表中是不可访问的数据，不能在工作表中动态捕获和共享。
-
JeremiahHorstick ✭✭✭✭✭✭这可以通过API来完成。
-
彼得Spung ✭✭
如何使用公式中的API ?
或者用另一种方式问……API对我们大多数人来说是一个沉重的负担。我们必须获得批准才能使用组织支持的语言编码平台，获得访问表格的API权限，然后有测试工具进行调试，然后学习一门额外的语言(我们已经学习了公式语言)，等等。我们已经在Smartsheet中获得了使用公式的授权，并且知道如何使用它们。而且，我们希望API调用的结果最终回到Smartsheet单元格中，而不是在语言环境的内存空间中。就像这个社区的问题一样——“我想在这个或另一个工作表中查找某个单元格在这个日期(或公式)的先验值，并在公式所在的这个单元格中返回结果”. ...为什么不让每个API函数都在公式中可用呢?一旦做出决定，这对Smartsheet来说确实是一项艰巨的任务。这可以分阶段完成——从请求最多的功能或使用最多的API开始(Smartsheet无疑会在其网站日志中跟踪API的使用情况)。
无意冒犯——我在社区中经常看到这样的回答——“只使用API”。这样做应该变得更简单，更容易上手。
任何想法吗?彼得
附注:是的，我会为此写一个增强请求。
-
彼得Spung ✭✭
两个新的产品增强ob欧宝娱乐app手机下载请求正在进行中:
提供一个公式来查找先前的单元格值:https://community.smartsheet.com/discussion/100795/formula-to-lookup-prior-cell-values/
使api作为公式可用:https://community.smartsheet.com/discussion/100785/call-apis-from-a-formula/
谢谢你，彼得·斯潘(电子邮件保护)| @paspung
帮助文章参考资料欧宝体育app官方888
类别
=SUMIF([Part number]$185:[Part number]$369, CONTAINS([Part number]167, @cell), Quantity$185:Quantity$369)<\/code><\/strong><\/p>
Like that it is working partially, because as I have sub assemblies, I will need to take the quantity of the PARENT() and multiply it to have the correct value.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":106651,"type":"question","name":"Incorrect Argument Set with nested IF","excerpt":"Counting number of occurances where month\/year is 1\/23 AND where cell within the row does not contain \"Color Add\" =COUNTIFS({New Product Status (Imagen) Go Live Launch Date}, IFERROR(MONTH(@cell), 0) = 5, {New Product Status (Imagen) Go Live Launch Date}, IFERROR(YEAR(@cell), 0) = 2023, {New Product Status (Imagen) new or…","categoryID":322,"dateInserted":"2023-06-20T18:01:37+00:00","dateUpdated":"2023-06-20T19:20:48+00:00","dateLastComment":"2023-06-21T13:22:46+00:00","insertUserID":74386,"insertUser":{"userID":74386,"name":"Stephanie Kiecker","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20Kiecker","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!W2nUX5neWjw!6djX-omKb7Q!x0GPDTlMr6q","dateLastActive":"2023-06-21T13:31:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":74386,"lastUserID":150413,"lastUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-21T16:58:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":37,"score":null,"hot":3374639663,"url":"https:\/\/community.smartsheet.com\/discussion\/106651\/incorrect-argument-set-with-nested-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106651\/incorrect-argument-set-with-nested-if","format":"Rich","lastPost":{"discussionID":106651,"commentID":381471,"name":"Re: Incorrect Argument Set with nested IF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381471#Comment_381471","dateInserted":"2023-06-21T13:22:46+00:00","insertUserID":150413,"insertUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-21T16:58:28+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":"2023-06-21T11:33:23+00:00","dateAnswered":"2023-06-20T20:32:28+00:00","acceptedAnswers":[{"commentID":381387,"body":"
The @cell part at the end is wrong. It will need to be @cell = \"Color Add\", but in doing that, you will need to adjust your NOT(CONTAINS part. Try replacing it with NOT(@cell = \"Color Add\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":106656,"type":"question","name":"Incorrect Argument - Second set of eyes please","excerpt":"I'm not sure what I'm missing but would appreciate some help with the below formula it is giving me an incorrect argument set and I can't figure out why. =COUNTIFS({Greet}, OR(@cell = \"yes\", @cell = \"NA\"), {Week Beginning}, [Report Date]@row, {Monitor Type}, \"Monitor\") \/ COUNTIFS({Greet}, OR(@cell = \"yes\", @cell = \"no\",…","categoryID":322,"dateInserted":"2023-06-20T18:34:48+00:00","dateUpdated":null,"dateLastComment":"2023-06-21T12:22:01+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T17:20:14+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-21T17:23:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3374638009,"url":"https:\/\/community.smartsheet.com\/discussion\/106656\/incorrect-argument-second-set-of-eyes-please","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106656\/incorrect-argument-second-set-of-eyes-please","format":"Rich","lastPost":{"discussionID":106656,"commentID":381461,"name":"Re: Incorrect Argument - Second set of eyes please","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381461#Comment_381461","dateInserted":"2023-06-21T12:22:01+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-21T17:23:52+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":"2023-06-21T12:12:06+00:00","dateAnswered":"2023-06-21T12:01:53+00:00","acceptedAnswers":[{"commentID":381446,"body":"
Syntax is right, so the other thing that can cause that error is range sizes not matching. Double check your cross sheet references to ensure they are all the same size and shape.<\/p>
<\/p>
I have trouble sometimes with this when I am referencing a large sheet or my internet is behaving poorly. Sometimes it takes a few extra seconds for the sheet to fully load in when you are selecting the range for the cross sheet reference. When the sheet does fully load in, the selection will always revert to the \"home cell\" or the cell in the top right corner. Even if you have already clicked on a column header. Then I have to go back and edit the reference and pause for a few seconds to let the sheet load into the window. You should be good to go when the sheet name appears near the top left of the window.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&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":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
