Rolling Current Month Weekly Report
Hi!
I'm hoping someone has a potential solution for what I'm trying to accomplish:
I have 500 sheets that I am rolling up into 1 Sheet Summary Report. Each of the 500 sheets has summary data that is included in the Summary Report. The Summary Report needs to be sent to the business owners on a weekly basis. In the report I need to include the data from the previous week for the current month. If we are in the middle of the month, I would need to provide all of the previous weeks' information.
For example -
- Monday, April 3rd would include April 1st to April 2nd
- Monday, April 10th would include April 1st to April 9th
- Monday, April 17th would include April 1st to April 16th, and so on until we got to May then it would start fresh because a cumulative report for the previous month is sent
I was hoping that I could use created date (added a filter on Created to be between 4/1/23 to 4/6/23 but the information is not displaying). I'm suspecting that the Created Date is when the Summary Sheet was created and not when the new row was added. I have confirmed I have rows in the sheet with a Created Date in the range.
Any guidance would be greatly appreciated.
Thanks
Carol-Anne
Best Answer
-
Hi Carol-Anne,
I've built a few solutions that report MTD YTD etc. You're not going to like this but the best way is to create a checkbox helper column in each sheet to identify rows in the current month. i.e.
=IF(AND(YEAR([created date]@row)=YEAR(TODAY()),MONTH([created date]@row)=MONTH([created date]@row)),1)
The in your summary sheet Create a MTD version of your formula, e.g.
=IF([helper]:[helper]=1,<<Formula>>)
OR
=SUMIF(<<column>>,[helper]:[helper],1)
I realize this sounds like a lot of work, the way around that is to purchase Smartsheet's premium product 'Control Center' which enables global changes across sheets... so yeah... a lot of work or a lot of money.
Let me know if you need anymore help.
Answers
-
Hi Carol-Anne,
I've built a few solutions that report MTD YTD etc. You're not going to like this but the best way is to create a checkbox helper column in each sheet to identify rows in the current month. i.e.
=IF(AND(YEAR([created date]@row)=YEAR(TODAY()),MONTH([created date]@row)=MONTH([created date]@row)),1)
The in your summary sheet Create a MTD version of your formula, e.g.
=IF([helper]:[helper]=1,<<Formula>>)
OR
=SUMIF(<<column>>,[helper]:[helper],1)
I realize this sounds like a lot of work, the way around that is to purchase Smartsheet's premium product 'Control Center' which enables global changes across sheets... so yeah... a lot of work or a lot of money.
Let me know if you need anymore help.
-
@BullandKhmer - thank you for the suggestion. Looks like I have a lot of work ahead of me! I'm trying to get our company to purchase Control Center because it would be so much better but hopefully I will have room in my budget next year.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives