weekly report

[ajm]

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.

 

[rondeondo]

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]

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]

get the data to the point you can get what yo need with minimal messing around then just do an output sheet for presentation.