You can download/install the morefunc add-in to make use of the INDIRECT.EXT
which will work with closed workbooks. Some other methods lined out in my
post he http://www.mrexcel.com/board2/viewtopic.php?t=123529


Regards,
Zack Barresse


"Harald Staff" wrote in message
...
Hi Bigweed

For "internal formulas", sheets within the file, it's done with INDIRECT.
But for external files it can't be done as far as I know.

HTH. Best wishes Harald

"Bigweed" skrev i melding
...
Hi, hopefully someone can help me with the following problem.
I have a workbook which has 5 worksheets of salesmans figures for the
current month. I compare this data with year to date figures, last years
figures for the same month, and last years year to date figures. This is
done
on each worksheet, each sheet representing a different salesman. All
comparison figures are on a different workbook, and a formula is used to
insert comparison figures into the current months sheet, eg if current
month
is Feb 05, and I need to refer to Feb 04:
=X:\KMF\Monthly Report\YE 2005\[Monthly Report KMF Feb
05.xls]Customers'!B8

At the moment, I have to change the formula which refers to the the other
workbooks manually on each worksheet, ie if current month is Mar 05, I
need
to change cell references for previous years figures to Mar 04. This is
because I open last months workbook, and save it as current month.
However,
because of this the cell references still relate to the previous months
comparison figures. Is there any way that I can just type into a cell
which
month Excel should refer to for the comparison figures? Excel can then
change
the formulas automatically by referring to this cell to see which months
figures are being compared against.

All help most appreciated
Thanks
Bigweed