Microsoft Microsoft office excel 2007 ac...

I would like to no how to change a date automatically in a formula, We recieve Sales Reports and i want to pull all the information into my excell spreadsheet, what i do is save it in my documents and my formula i place into my cell is salesreport201209.xls ='C:\Users\Liane\Documents\[salesreport201209.xls]Sales Sheet'!$I$8

What i want to do is place this in every cell but i dont want to have to go through and update the date in every cell, this would take many days.. i was wondering if anyone new how to keep that exact formula and it automatically updating the date evey 7 days? just like if i was to place just the date into the cell and pull it down it would automatically form a date in each cell down the page!

Any help would be great.

Edit: Subject changed by Office forum moderator

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:

=IF(MOD(TODAY()-A1,7)=0,TODAY(),(DATEVALUE("01/01/2010")-(WEEKDAY(DATEVALUE("01/01/2010"),2))+(WEEKNUM(TODAY()-7,1)*7)))

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:

=(TEXT(DAY(A2),"00")&TEXT(MONTH(A2),"00")&RIGHT(YEAR(A2),2))

On my sheet this returns

240110which 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.

Ask Your Question

Weekly Poll