If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using SaveAs
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. |
#2
|
|||
|
|||
Using SaveAs
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. |
#3
|
|||
|
|||
Using SaveAs
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. |
#4
|
|||
|
|||
Using SaveAs
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. |
Thread Tools | |
Display Modes | |
|
|