I have a large excel file that I update with weekly sales data, every week. I have a table that populates based off of a pivot table. We often look at key time periods other than just the last week (aka Last 4 Weeks, Last 8 Weeks, etc). So far, each week, I have been manually dragging the sum area down so it represents the last 4, 8, etc. weeks. For example (Where weeks 9 and 10 have not ocurred yet):


Week $ Sales
1 $100.00 L4 $2,300.00
2 $200.00 L8 $3,300.00
3 $300.00
4 $400.00
5 $500.00
6 $600.00
7 $600.00
8 $600.00
9 $0.00
10 $0.00


What formula can I add so that, when Week 9 occurs and the data gets added, the formula will automatically adjust to sum Weeks 6-9 (for L4) and 2-9 for L8? In essence, I need both the beginning value and the end value to be dynamic.


Thanks to anyone who helps! I'm new to the thread, so if this has been discussed before, sorry!!