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

Find more posts tagged with

Accepted answers

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>>)

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

All comments

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>>)

=SUMIF(<<column>>,[helper]:[helper],1)

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.