Linked Cells not updating in report
I have created a dashboard to see the daily job status. One of the functions is to see how many jobs are open on Today's task list. This is based on countifs on the date of the task list.
The formula's are sometimes working, sometimes not. When we make an update on the tasklist, the report does not show the latest updated data, but the figures go back to the nrs from the day before.
Is there a delay in updating? The tricker seems to be that when a change is made, instead of showing the updated data, is shows old data. Anyone having similar issues, and more important, any idea how to solve this?
Best Answer
-
Genevieve P. Employee AdminCan you copy/paste exactly what formula you're using? It sounds like you'll have the TODAY function somewhere in your formula.
Keep in mind that the TODAY function needs an update or Save to happen to the sheet that houses the formula in order for it to recognize what the Date is Today.See this articlewhere it states the following:
"Smartsheet will update all instances of the TODAY function in a sheet to the current date when any of the following actions are taken:
- You open the sheet and save it
- You add a new row to the sheet from a form submission
- You make changes to the sheet from an update or approval request
- You update the sheet by way of a report
- A linked cell updates the sheet
- You open the sheet being referenced in a cross-sheet formula "
If your formulas are on a separate sheet than your data, one of the previous actions will need to happen on this separate sheet.
I would suggest putting your formulas in to a SheetSummary Field(see here) to keep it on the sheet where the new rows are created.
Then you can select Sheet Summary Fields as a source for Metrics in a Dashboard:
Let me know if this makes sense and has resolved the issue you were seeing!
Cheers,
Genevieve
Answers
-
Genevieve P. Employee Admin
Can you copy/paste exactly what formula you're using? It sounds like you'll have the TODAY function somewhere in your formula.
Keep in mind that the TODAY function needs an update or Save to happen to the sheet that houses the formula in order for it to recognize what the Date is Today.See this articlewhere it states the following:
"Smartsheet will update all instances of the TODAY function in a sheet to the current date when any of the following actions are taken:
- You open the sheet and save it
- You add a new row to the sheet from a form submission
- You make changes to the sheet from an update or approval request
- You update the sheet by way of a report
- A linked cell updates the sheet
- You open the sheet being referenced in a cross-sheet formula "
If your formulas are on a separate sheet than your data, one of the previous actions will need to happen on this separate sheet.
I would suggest putting your formulas in to a SheetSummary Field(see here) to keep it on the sheet where the new rows are created.
Then you can select Sheet Summary Fields as a source for Metrics in a Dashboard:
Let me know if this makes sense and has resolved the issue you were seeing!
Cheers,
Genevieve
-
@Genevieve Pmany thanks. I now put all formula's on one data sheet, and my dashboard pulls all data from there. Indeed when I go into the data sheet and save it, the dashboard is working. Not an ideal solution, but it works with minimum effort.
-
Andrée Starå ✭✭✭✭✭✭I hope you're well and safe!
To add to Genevieve's excellent advice/answer.
Please have a look at my post below with a method I developed to update the sheet(s) daily.
More info:
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Help Article Resources
Categories
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/1R2CO9WEE5M5\/screenshot-of-ghost-browser-282021-01-06-2c-14-22-21-29.jpg<\/a><\/p>This could also be structured so you'd use a so-called helper sheet for the process above and then link it to all other sheets where you'd need the daily update.<\/em><\/p>Let me know if you have any questions!<\/p> I hope that helps!<\/p>Stay safe, and Happy New Year!<\/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 everyone,\\n\\nHappy New Year!\\nI hope you're well and safe!\\n\\nI've developed a way to use the newly released \"},{\"attributes\":{\"bold\":true},\"insert\":\"Record a date \"},{\"insert\":\"feature to update sheets with today's date automatically without the need to open it or similar.\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"1.\"},{\"insert\":\"\\nAdd a so-called helper column. (Date)\\nWe can call it Today Helper and add a date before the current day.\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"2.\"},{\"insert\":\"\\nYou'd then use an Automation Workflow as described below.\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Trigger:\"},{\"insert\":\" When a date is reached\\nEvery day starting on 2021-01-06 \"},{\"attributes\":{\"italic\":true},\"insert\":\"(today or earlier)\"},{\"insert\":\"\\n02:00 \"},{\"attributes\":{\"italic\":true},\"insert\":\"(select the time that makes the most sense for the process)\"},{\"insert\":\"\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Conditions\"},{\"insert\":\"\\nWhere\"},{\"attributes\":{\"bold\":true},\"insert\":\" Today Helper\"},{\"insert\":\" is in the past\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Record a date\"},{\"insert\":\"\\nRecord a date in Today Helper\\n\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/1R2CO9WEE5M5\\\/screenshot-of-ghost-browser-282021-01-06-2c-14-22-21-29.jpg\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/1R2CO9WEE5M5\\\/screenshot-of-ghost-browser-282021-01-06-2c-14-22-21-29.jpg\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\\n\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"This could also be structured so you'd use a so-called helper sheet for the process above and then link it to all other sheets where you'd need the daily update.\"},{\"insert\":\"\\n\\nLet me know if you have any questions!\\n I hope that helps!\\nStay safe, and Happy New Year!\\nBest,\\n\"},{\"attributes\":{\"bold\":true,\"link\":\"mailto:andree@workbold.com\"},\"insert\":\"Andrée Starå\"},{\"insert\":\" | Workflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/workbold.com\\\/\"},\"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":"2021-01-06T13:40:12+00:00","insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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\/74527\/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar","embedType":"quote","name":"Daily Update Solution without the need to open the sheet, use Zapier, or similar"}"> https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/1R2CO9WEE5M5\/screenshot-of-ghost-browser-282021-01-06-2c-14-22-21-29.jpg\"){kind=link}