Referencing one sheet with date ranges to change all SUMIFS and COUNTIFS at once
Hello! Every month we update multiple project items once the previous month of data is collected. Up until now, we have gone into each projects metric sheet to update, changing the date range to pull the recent data.
Instead, I would like to build one reference sheet, populated with every day of the year in 12 month columns, so that by simply changing a month pointer on a metric sheet, it will find the range of days for that month on the reference sheet and change all the formulae on that sheet, no matter what other criteria is in that formula.
I've tinkered with the INDEX/MATCH functions, but the ranges for the other items vary from sheet to sheet so I'd still have to go into each project to get them to fire correctly.
First time question. I've used the Community forums to solve other problems and this place is a great resource for solutions, so thanks! Apologies if this was asked in another thread.
Answers
-
Hi @Jeff Widrig
I hope you're well and safe!
Here's a possible workaround or workarounds
- Create a Report showing the Dates from each Metric Sheet and change them there.
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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Andrée, thank you for the suggestion! I've not found a way to change data displayed on a report without changing it on the original sheet. How does that work? I thought a report was just one-way, to filter and display a sheet for other users or on a Dashboard.
Even with that, it still seems like the same amount of work required to change all the source sheets individually. What I'm looking for is a piece of formula that I can use universally that will do the following:
I import an Excel file with the last month of data and add it to the Master sheet using 'Copy to Another Sheet'. It includes a column that is set as a Date column type. I then go into the main metric sheet for that project and change one cell. When that cell changes (exa. from April to May) the formula on the sheet that use that cell as a criterion will look to the month reference sheet and, instead of looking for
FIND([Primary Column]5, @cell) > 0, {Reference Date}, >=DATE(2022, 4, 1), {Reference Date}, <=DATE(2022, 4, 31))
...it will instead look for
FIND([Primary Column]5, @cell) > 0, {Reference Date}, >=DATE(2022, 5, 1), {Reference Date}, <=DATE(2022, 5, 31))
and find all the sums with the new month that match the other criteria. This changes the Sum, which updates to a Dashboard metric or chart widget.
Is this possible? Thanks again for the response!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!