Help with a weekly updated formula
Hello! I'm still a beginner smartsheets user, and while I am decent with excel the live updates is still very new. I'm trying to create a formula that will pull information on a weekly basis, M-F, that updated for each week and didn't pull from the previous week at all. For example in this screenshot, I would like a formula where I can select the whole column, but only grab information from this week, 1/26 through 2/1. And then as soon as we hit 2/2, I'd like it to update to information from 2/2 through 2/8. Is this possible and can you help me with what that formula would look like?
Answers
-
There may be other ways to do this - but the simplest way to me would be to add a helper column and add the formula:
=WEEKNUMBER(Day@row)
The in the Source sheet - add a Today column and have an automation run daily that will update the date on every row to Today's date. This is important if you don't edit the sheet daily. There's a little issue with Smartsheet and using the TODAY() function when you are not in your sheet on a regular basis.
I would add the helper column in your source sheet as well =WEEKNUMBER(Today@row)
Then when you do the Index/Match formula you are matching the Weeknumber from your Target sheet to the Weeknumber in your source sheet. This will create an "automatic" changing of the data pulled.
Does that make sense?
I would need a little more information about what is in your source sheet - and I could help you with the INDEX/MATCH or INDEX/COLLECT formula if you need it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!