Changing cell references automatically

[Guest]

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

 

[Harald Staff]

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

 

[zackb]

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 here: http://www.mrexcel.com/board2/viewtopic.php?t=123529


Regards,
Zack Barresse

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

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

 

[Franz]

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.

If your workbook have always the same structure (i.e. the names of the
worksheets are always the same...) from month to month, I think you can just
change the link from Edit => Link.

Hoping to be helpful...

Regards