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