Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Br. Columbia, Canada
    Posts
    109
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Total many sheets to one (M$ 2003)

    I have many (15) sheets, identical in format that I need to total on a single separate sheet. Each sheet has 35 columns.
    On my summary (totals) sheet I started with the first column/cell (B3) I wanted to total starting with = then clicking on sheet 1 and clicked B3 added + then clicked on sheet 2 B3 added + then clicked on sheet 3 etc.
    That seems to work just fine for the first column. But I'm wondering if anyone knows a quicker way of doing this for the other 34 columns. I tried copying from B3 to C3 on the summary sheet but got a wrong answer.
    Appreciate any suggestions. Thanx

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total many sheets to one (M$ 2003)

    I have just done the same as you on the attached file (summing sheets 2 and 3 in Sheet1. I entered the formula into cell A1 then used Fill Down and Fill Right.

    Your approach as stated should work.
    Attached Files Attached Files
    Regards
    Don

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total many sheets to one (M$ 2003)

    HI Joan:

    Since you say all your sheets are laid out identically, you can use =SUM() to add up the same cell on every sheet.

    The formula is =SUM(Sheet1:Sheet15!B3), which sums a single cell on a range of sheets, rather than summing a range of cells on a single sheet. This formula can then be copied down/across for the other cells.

    Rather than type the formula in, it might be easier (and less prone to error!) to use the mouse:

    1. Select the cell where you want the total and type =SUM(
    2. Click on the tab for the first sheet.
    3. Hold down SHIFT and click on the tab for the last sheet. The tabs for all the sheets you have selected turn white.
    4. Click on the Cell you want to add up and type the closing )
    5. Press ENTER. It is important NOT to click back on the sheet you started from.

    Hope this helps.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Br. Columbia, Canada
    Posts
    109
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Total many sheets to one (M$ 2003)

    Thank you Don & Tony.
    Don your attached spread sheet is very similar to what I started to do, but kept getting an error message about the formula. or missing a sheet so the results were not correct.
    Tony's solution works extremely well, once I figured it out.
    Thanx again both of you.

Posting Permissions

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