copying a sum formula across worksheets

joneric · Apr 19th, 2002, 11:11 PM

I am creating worksheets that have MTD columns and YTD columns.
I am using the formula =SUM('march'!b1+a1) where b1 is the YTD total from the march worksheet and a1 is the MTD total from the april worksheet.
When I copy the worksheet to create one for a new month, I want the new worksheets formula to automatically be =SUM('march'!b1+'april'!b1+a1)
But what I am getting is that the formula remains =SUM('march'!b1+a1)
How do I do this???

[ This Message was edited by: joneric on 2002-04-19 22:19 ]

Ian Mac · Apr 20th, 2002, 02:28 AM

Create two sheets, say YearStart and YearEnd.
these should be copies of your Month Sheets and either side of your month sheets i.e. YearStart,January,February,March,YearEnd.

on your YearSummary Sheet in the correct cell put =SUM( Click on the first Sheet And hold Shift And select the Last Sheet a Click in the Cell you need to sum.

press ',' then click in the cell on the summary that you need.

you should end up with something like this:

=SUM(YearStart:YearEnd!B1,YearSummary!A1)

now every time you need to add a sheet make sure it's after the last month and BEFORE the YearEnd sheet and it will be included in the SUM().

You can also hide these sheets and it will still work for the formula.

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-04-20 01:29 ]

[ This Message was edited by: Ian Mac on 2002-04-20 01:30 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 11:11 PM	#1
joneric New Member Join Date: Apr 2002 Posts: 1	I am creating worksheets that have MTD columns and YTD columns. I am using the formula =SUM('march'!b1+a1) where b1 is the YTD total from the march worksheet and a1 is the MTD total from the april worksheet. When I copy the worksheet to create one for a new month, I want the new worksheets formula to automatically be =SUM('march'!b1+'april'!b1+a1) But what I am getting is that the formula remains =SUM('march'!b1+a1) How do I do this??? [ This Message was edited by: joneric on 2002-04-19 22:19 ]

Apr 20th, 2002, 02:28 AM	#2
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	Create two sheets, say YearStart and YearEnd. these should be copies of your Month Sheets and either side of your month sheets i.e. YearStart,January,February,March,YearEnd. on your YearSummary Sheet in the correct cell put =SUM( Click on the first Sheet And hold Shift And select the Last Sheet a Click in the Cell you need to sum. press ',' then click in the cell on the summary that you need. you should end up with something like this: =SUM(YearStart:YearEnd!B1,YearSummary!A1) now every time you need to add a sheet make sure it's after the last month and BEFORE the YearEnd sheet and it will be included in the SUM(). You can also hide these sheets and it will still work for the formula. _________________ Share the wealth!! Ian Mac [ This Message was edited by: Ian Mac on 2002-04-20 01:29 ] [ This Message was edited by: Ian Mac on 2002-04-20 01:30 ]