Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding A1 in ALL worksheets. (97)

    I collate daily figures, with each day on a separate worksheet. Sheets are named mon01,tue02, wed03 for example. (the number bit being the date).

    In my summary worksheet I need to add the totals of say A1 in all the daily sheets.
    I could do =mon01!A1 + tue02!A1... etc, for all 30 or so sheets.

    But goddamit this formula would be so long-winded- and besides there wouldn't be any point in putting it into my permanent template anyway, cos the next month the sheet names will be totally different.

    Is there any easy way of adding say A1 in all worksheets (except my summary sheet obviously).

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Adding A1 in ALL worksheets. (97)

    I'd build the formula using the point + click method (my goal - make the computer type).
    Start your formula in your summary sheet by typing in
    =sum(
    Then click on the first sheet you want to add from and click on the specific cell you want to add. Then hold down your shift key and click on the last sheet tab of the range you want to add. Excel will build the following formula for you.
    =sum(sheet1:sheet30!a1)
    Just press your enter key to accept the formula. All done!

    This does assume that you want to add cell A1 in all sheets, otherwise you'll have to click on each cell you want to add and place a comma between each cell reference.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding A1 in ALL worksheets. (97)

    You can use the SUM() function in a 3D sort of way if you have all the sheets you want summed aligned consecutively by tab order. Suppose you have 31 sheets representing 31 days in a month, you could use <pre> =SUM(Mon01:Wed31!A1)</pre>

    provided that Mon01 is the first sheet and that Wed 31 is th elast of the sheets you want summed. The formula would have to be placed in a sheet either before the first (Mon01) or after the last (Wed31).

    Andrew C

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding A1 in ALL worksheets. (97)

    > =SUM(Mon01:Wed31!A1)

    Just thinking aloud... I think what I'll do is create an extra 2 sheets- start! and end! and place my 30 daily sheets between them. Then my formula can read =SUM(start!:end!A1).
    That way I won't have to change the formula each month.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding A1 in ALL worksheets. (97)

    CJ,

    Good idea - but make sure you do not include the exclamation character after Start. Use <pre><big>=SUM(Start:End!A1)</big></pre>


    Andrew C

Posting Permissions

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