Reference to a linked file

[Mike D.]

Hi. I have two files to which I would like to link cells to. Each week, the
file names will change based on the week number.

The name of one file is the current week (for example Week 3.xls).

The name of the other file is always one less than the first (for example
Week 2.xls).

Is there a way to do math within the cell reference?

 

[Dave Peterson]

Maybe...

You could use some functions to find the number (like: =search(), =mid(),
=left(),...).

But the bad news is that you're going to have to use =indirect() to surround
your formula that evaluates to the workbook name/worksheet name/range address.
And =indirect() will only work if the sending workbook is open.

But the good news is that Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that allows you to return values from closed
workbooks.

===
If none of this helped and you want to try, you'll want to post the real
workbook names so that the formulas can get built correctly.