I bet you could use this, too:
=text($c$3-7,"dd-mm-yy")

But if that sending workbook is closed you'll get an error.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.


ridingpeter wrote:

I have a weekly balance shhet saved by the date in the filename. Each week
the date changes so I have to manually changed the date in the cell address
to get the value. I want to use the curent date take 7 days off add some
text so it comes up with the file name. I can get the correct text but i get
a &ref error

the date in C3 is 16/05/09 and i want to create ='[09-05-09
test.xls]Sheet1'!$B$3 to get the value from last week

using ='["&TEXT(DATE(YEAR($C$3),MONTH($C$3),DAY($C$3)-7), "dd-mm-yy") & "
test.xls]sheet1'!$B$3

keep getting #REFF error.

Anyone can you help

--

Dave Peterson