Changing a linked formula ... ... ...
 

Hi there and thanks in advance for any assistance.

I have a workbook that has daily/weekly/monthly data. As a new week
elapses, I change the date in a cell, which changes all of the dates to the
right "=(L9+1)" {where L9 is my updated cell}, as well as the week ending
column names ="WE "&(TEXT(BN9,"m/dd")). As I change the date, every week
moves to the left over the previous week. All that is visiable is a rolling
5 weeks of data.

I have a file that I save from another source each week (saleswe0930.xls,
saleswe1007.xls .. .. .. ). The working file is linked to the same 5 cells
on every weekly file to pull 5 days of sales results.

I'm looking to avoid changing the linked file for all 7 weeks, every week
when I update. Is it possible for the linked cells to update pulling in the
correct Weekending date?

Example,

This cell (below) would need to change to
Original
'Z:\[saleswe0930.xls]Sheet 1'!$Q$8
Updated
'Z:\[saleswe1007.xls]Sheet 1'!$Q$8


Thank you,
Mike