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.