Hi,

I use a SaveAs feature for a report. there are 4 sheets in this work book in
total. The last 2 pages are summary sheets from the previous year. If i use
the following formula it changes the previous months data as I have changed
it in the Worksheet.

=Summary!C11 For Report March
=Summary!C11 For Report April

What would i enter to keep each cell using the data from the correct months
workbook. I use SaveAs the previous month and change the current data for the
current month.

Mar-10 2 1 50% $0 0
Apr-10 2 1 50% $0 0

I hope this explains what I am after.

Thanks for your help in advance.

Ok, as I understand it, you finish a month and use SaveAs to save it and then
return to using the original book (which I'll call "current book")? If
that's the case:

With the current book open, open up the previous month's book. Go to the
cell in the current book where you need to revise the formula, choose that
cell and clear out the previous =Summary!C11 formula and then just put an =
symbol into that cell.
Next select the previous month's workbook, go to the Summary sheet and click
in cell C11 and press the [Enter] key. You can now close the previous
month's workbook. Excel will finish up the formula for you with a complete
reference to the previous month's book/sheet/and cell.

Now, that's going to LINK to that previous month's workbook, so if you ever
have to change the data in it for some reason (clerical error?) the data in
the current workbook will update automatically.


"SarahN" wrote:

Hi,

I use a SaveAs feature for a report. there are 4 sheets in this work book in
total. The last 2 pages are summary sheets from the previous year. If i use
the following formula it changes the previous months data as I have changed
it in the Worksheet.

=Summary!C11 For Report March
=Summary!C11 For Report April

What would i enter to keep each cell using the data from the correct months
workbook. I use SaveAs the previous month and change the current data for the
current month.

Mar-10 2 1 50% $0 0
Apr-10 2 1 50% $0 0

I hope this explains what I am after.

Thanks for your help in advance.

Is there a way to enter this formula without having to go back each month and
change it?



"JLatham" wrote:

Ok, as I understand it, you finish a month and use SaveAs to save it and then
return to using the original book (which I'll call "current book")? If
that's the case:

With the current book open, open up the previous month's book. Go to the
cell in the current book where you need to revise the formula, choose that
cell and clear out the previous =Summary!C11 formula and then just put an =
symbol into that cell.
Next select the previous month's workbook, go to the Summary sheet and click
in cell C11 and press the [Enter] key. You can now close the previous
month's workbook. Excel will finish up the formula for you with a complete
reference to the previous month's book/sheet/and cell.

Now, that's going to LINK to that previous month's workbook, so if you ever
have to change the data in it for some reason (clerical error?) the data in
the current workbook will update automatically.


"SarahN" wrote:

Hi,

I use a SaveAs feature for a report. there are 4 sheets in this work book in
total. The last 2 pages are summary sheets from the previous year. If i use
the following formula it changes the previous months data as I have changed
it in the Worksheet.

=Summary!C11 For Report March
=Summary!C11 For Report April

What would i enter to keep each cell using the data from the correct months
workbook. I use SaveAs the previous month and change the current data for the
current month.

Mar-10 2 1 50% $0 0
Apr-10 2 1 50% $0 0

I hope this explains what I am after.

Thanks for your help in advance.

Yeah, When I am preparing the current month I Save As the previous month and
re name it with the current month. I then change the data to reflect the new
information. I am trying to avoid having to recalculate the summary sheets
every month by entering formulas. my problem has been when i have formulas
that just reflect information in that worksheet.

Thanks heaps. I see what your reccomendations Work. So just wondering if
there is a way to enter these formulas to lock them in.

"JLatham" wrote:

Ok, as I understand it, you finish a month and use SaveAs to save it and then
return to using the original book (which I'll call "current book")? If
that's the case:

With the current book open, open up the previous month's book. Go to the
cell in the current book where you need to revise the formula, choose that
cell and clear out the previous =Summary!C11 formula and then just put an =
symbol into that cell.
Next select the previous month's workbook, go to the Summary sheet and click
in cell C11 and press the [Enter] key. You can now close the previous
month's workbook. Excel will finish up the formula for you with a complete
reference to the previous month's book/sheet/and cell.

Now, that's going to LINK to that previous month's workbook, so if you ever
have to change the data in it for some reason (clerical error?) the data in
the current workbook will update automatically.


"SarahN" wrote:

Hi,

I use a SaveAs feature for a report. there are 4 sheets in this work book in
total. The last 2 pages are summary sheets from the previous year. If i use
the following formula it changes the previous months data as I have changed
it in the Worksheet.

=Summary!C11 For Report March
=Summary!C11 For Report April

What would i enter to keep each cell using the data from the correct months
workbook. I use SaveAs the previous month and change the current data for the
current month.

Mar-10 2 1 50% $0 0
Apr-10 2 1 50% $0 0

I hope this explains what I am after.

Thanks for your help in advance.