Rolling Current Month Weekly Report

Carol-Anne Cerbone

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

BullandKhmer

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

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.

Carol-Anne Cerbone

@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.