use formular to create a cell address
 

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

use formular to create a cell address
 

I cannot test this as I have not the time to make files with odd names
but you need to use the INDIRECT function as in
=INDIRECT("'["&TEXT(DATE(YEAR($C$3),MONTH($C$3),DAY($C$3)-7), "dd-mm-yy") &
"test.xls]sheet1'!$B$3")
I would break this up,
placing ="'["&TEXT(DATE(YEAR($C$3),MONTH($C$3),DAY($C$3)-7), "dd-mm-yy")part
is cell A1 (say)
and sheet name ="test.xls]sheet1'!" in A2 (say)
then =INDIRECT(A1&A2&"$B$3")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ridingpeter" wrote in message
...
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

use formular to create a cell address
 

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