Summary formula
Hey I'm trying to do this in a Sheet Summary:
- If [Date] is today, then show me the value in the same row in column G.
This works for an individual row (1)
=IF([Date]1 = TODAY(), [G]1, 0)
But I can't get it to work with @row to search all the way down the sheet to look for the current date, I've tried these:
=IF([Date]@row = TODAY(), [G]@row, 0) (unparseable)
=IF([Date]@cell = TODAY(), [G]@cell, 0) (unparseable)
=IF([Date]:[Date] = TODAY(), [G]:[G], 0) (invalid operation)
Also I saw some posts that TODAY doesn't work unless you open/edit the sheet. Is this correct and is it true when used in the Summary fields?
Answers
-
Hi
I would recommend an Index(Match()) type formula.
=INDEX(G:G, MATCH(TODAY(), Date:Date, 0))
Here is a picture of an example working - today is 10th July.
If there's more than 1 row with todays date in it, it would return the first match only. There are other functions that could COLLECT all the correct G values and JOIN them together in a list (I have capitalised Join and Collect as they are the function names that you would need to use).
I hope this helps.
Kind regards
Debbie
Debbie Sawyer
CSSO (Chief Smartsheet Solutions Officer)
Smarter Business Processes
Smartsheet Community Champion & Ambassador
-
For the refreshing issues, I usually have a checkbox column hidden on the sheet called something like "Daily"
You can have a workflow that checks the column on a daily basis and if Daily is set to 1 then change the cell to 0 and if it is set to 0 then change it to 1.
This means that every day the system updates all the rows on your sheet to have either a checked box or an empty box and it forces the refresh on the today function.
You can leave this hidden on the sheet, it runs while you are off line and the sheet remains in sync with todays date.
Kind regards
Debbie
-
Thank you! The INDEX function worked perfectly.
I don't have the workflow plugin so I guess will have to edit the sheet
-
Ahh - are you a free collaborator? The admin on the sheet might be able to write you a workflow, or yes, just make sure you open the sheet daily! That will do it too!
-
Hi, no I am the admin on the sheet. I just thought I needed additional licenses to create workflows.
But it looks like I can; time to dig in there I guess!
Thanks again
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!