Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: copying a sum formula across worksheets

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •