Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Sum a single cell across 31 worksheets

    I have inherited a set of workbooks along with the task of summarizing their results, twelve workbooks with up to 31 worksheets in each. I'm looking to consolidate the total of columns C through G in row 11. On the 31st sheet a SUM exists in column AB already but you can imagine what it looks like (=AA11+'1-30'!AA11+'1-29'!AA11+'1-28'!AA11+'1-27'!AA11+ . . . . and so on).

    There are 24 columns (for Hours of Day). If I add a new sheet, click on C11, hit the plus key, select all sheets, then click on C11 in the first sheet, I get a formula that looks like this: =+'1-1:1-31'!C11, but it returns #REF.

    Is there a shortcut you can suggest to help me speed this process? I may be asked to do prior years and so far they're all looking the same.
    Last edited by Arcturus16a; 2012-02-21 at 17:51.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A 3D formula will work for contiguous sheets
    =SUM('1-31:1-01'!AA11)

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I pasted your formula as it appears here and changed AA to C to refer to the first column of data. Excel changes the formula to =SUM('1-31:[1-01]1-01'!C11) and results in #REF.

    I tried the Consolidate function just now stepping through all 31 sheets and the error popped up Cannot Create something or other.

    Could the problem be that the worksheet labelled 1-1 appears as Sheet1 in the Consolidate function All references box? All other sheets appear to be labelled '1-4 or '1-5 etc.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Steve, Please ignore the Consolidate function error. That was my fault. I got a reulting value but want to check it out to see if its really working the way I'm expecting.

    But that was very labor intensive and I have many more files to go. I'd really like to get your suggestion to work to save time. Any hints?

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Arcturus,

    Should work exactly as Steve said, it does for me at least in this limited example.

    FYI: Cell A1 contains the value of 5 on each of the dated sheets.
    Attached Images Attached Images
    Last edited by RetiredGeek; 2012-02-21 at 18:31.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Steve. I think I got it now. =SUM('1-1:1-31'!AA11)

    The 3D formula you showed me is correct and I think it works just fine after one simple modification. The original spreadsheet was created with non-standard date formats. You naturally (and rightly) assumed that the first sheet for the first day would start with a leading zero. Once I pulled that out of the formula the correct value (manually verified) popped up.

Posting Permissions

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