Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have created a workbook with 31 identical data entry sheets, 1 for each day of the week. The format is quite messy, and has to be that way, there are many formulas and calculations. I need to create a 32nd summary sheet, identical to the others that summarises the entire month.

    I'm looking for an easy way to achieve this without manually calculating each one. Any suggestions? Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='769127' date='03-Apr-2009 22:41']The format is quite messy, and has to be that way[/quote]
    I'd much prefer to use a single sheet with a database-like table. This makes is a lot easier to summarize the data in various ways.

    But you can use formulas like this to calculate across many sheets:

    =SUM('Sheet1:Sheet31'!B2)

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I completely agree, this is very messy, and in my eyes far from ideal! Thanks Hans.

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    When I copy and paste the formula into the formula bar for cell B2 on sheet 32, the formula converts to =SUM('Sheet1:[Sheet31]Sheet31'!B2) and shows #ref.

    Am I missing something?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the first place, think about where you want a total - the B2 in my example was just that: an example.
    But let's take B2 for illustration purposes.

    - Activate the summary sheet.
    - Select cell B2.
    - Start a formula by typing =SUM(
    - Click on the sheet tab of the first day.
    - You should see something like =SUM(Sheet1! where Sheet1 is the name of the sheet.
    - Hold down the Shift key and click on the sheet tab of the last day.
    - You should see something like =SUM(Sheet1:Sheet31!
    - Click on cell B2.
    - You should see something like =SUM(Sheet1:Sheet31!B2
    - Type the closing parenthesis ).
    - You should see something like =SUM(Sheet1:Sheet31!B2)
    - Press Enter or click the green check mark in the formula bar.

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I figured it out shortly after posting: >> =SUM('1:31'!H9), copied and pasted to all the required cells and then just changed the cell reference for each.

    Cheers Hans.

Posting Permissions

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