|I played with this a bit and it seemed to work...|
For this example, I'm going to assume that cells A1:A3 are free on the sheet that contains the formula. Adjust as required...
I'm also going to assume that since your example date (December 20, 2009) is a Sunday, you want the date updated every Sunday.
I'll break this into pieces to make it easier to explain. It could probably be combined into one long formula, but I'll leave that up to you.
In A1 I put 1/3/2010 which is the first Sunday of 2010.
In A2 I put:
This should update A2 so that it always shows the most recent "Sunday Date". e.g. as of today (1/27/2010) my sheet shows 1/24/2010, which was last Sunday. As of 1/31/2010, A2 should show 1/31/2010 until 2/7/2010 which is the next Sunday.
In A3 put this:
On my sheet this returns 240110 which I believe is the format you are using in your filename.
Finally, your formula should look like this:
=INDIRECT("'C:\Users\Liane\Documents\[salesreport" & A3 & ".xls]Sales Sheet'!$I$8"
The INDIRECT function builds a formula from text strings, so it should pick up the current value in A3 and build the filename.
Note the Double then Single quote before the C. The Double quote starts the text string which must include the Single string for the formula to work.
One caveat: The TODAY() function will not update just because your system clock changes from Saturday to Sunday. The dates will update the next time the sheet calculates or is opened or closed, etc. In other words, if you leave the sheet open from Saturday to Monday, it will still show the previous Sunday's date because TODAY() will not recalculate until the sheet recalculates.