weekly report

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,777
we have a new initiative at work and the powers that be would like to see an activity report each week. this will be a simple enough this week minus last week count across three segments. I wonder what anyone else has done in this space. I have begun to create a report file for each week such that each file will contain four tabs (one for each segment of the campaign and one extra on which I will pivot the segments to get a total). Each of these files will be in exactly the same format with tab names the same.

I was then going to create a Weekly Report File that would link back to each week's summary tab (the extra one) and display the summary pivots for each week. In this Weekly Report File, a second tab would have on it the difference between the latest two weeks, which would be the Activity Report.

while this will work, there is a lot of pfaffing about importing each new week and then changing the Activity report parameters to target the latest two weeks.

really hoping for some suggestions.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
Hi
I'd recommend having a single data sheet with a date and the relevant data you're storing, then make a pivot from that sheet. When you use the date field in the pivot you can choose to have it group by week or month or year etc... kinda depends on the source data as to how you set it up. Best to stay away from multiple files or even sheets with effectively the same data. If the pivot won't give you week grouping you can use =DATE(YEAR(G2),MONTH(G2),DAY(G2)+(1-WEEKDAY(G2))) to give you the first day of the week related to the date, where the date is in G2 in this instance. then use that column as your grouping data.
The source data for a pivot can contain many fields but you may only need to display a couple for your report.
Feel free to post a sample of your data with a mock up of the desired output and I'll see how close I can get.
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,777
Thanks for your input rondeondo. i am still messing around with the files but have to keep the final "exactly the way it is currently" because the ol' higher ups got to their positions of authority by not being flexible. sorry, sarcasm is my escape when dealing with directors.
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
get the data to the point you can get what yo need with minimal messing around then just do an output sheet for presentation.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,214
Messages
5,600,359
Members
414,380
Latest member
p1electric

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top