How can I pull weekly summaries a bit easier? (Example Attached)

**example spreadsheet attached

Weekly Summary Example.xlsx

I have multiple accounts that I keep track of. Each Account is put into a region which has its own tab. Each tab has corresponding dates listed down Column A. Along the rest of the columns are various fields.

My job is to create weekly summaries for each of the accounts.

So far the way I've done this is by creating a "table" (not an actual excel table, but formatted to look like one).
I've listed each of the accounts in the table and I've used a sum function to pull the values for the week.
Along side the table I've listed the row numbers for the range of dates I'm pulling. This is because when I pull down to copy the summary table for next week, the row numbers will auto-populate. I then highlight the formulas and do a 'find and replace' to quickly change all of the formulas correspond with the correct rows from the other tabs.

I know its a mouthful but I think if you look at the spreadsheet you will understand.

Note that I need a new weekly summary every week but I keep the last one so the drag down method seems to be the easiest.

My question is... is there ANY way that is smarter or easier execute this process?
My actual spreadsheet is much larger with a lot more fields for me to pull but this is how it works.

I am even open to organizing the data in a completely different way.

Are there any macros that could pull this? Would a pivot table help?


TL;DR: I need to pull data from various columns on various tabs for a specific range of dates. The dates (by day) are by row.
What is the easiest way to pull and present the data? Spread sheet attached for reference.


Thanks!!

the offset function is probably my favorite function in excel and should help you out quite a bit here:

Please Login or Register  to view this content.

put this in c31. it will sum a range which begins at the starting row in column h and extends down 6 days.

for your other values just change the starting column - 'CA Retailers'!$D$1 etc...