Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Warrenton, Virginia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use multiple pages (Office 97 suite)

    Is there a way to use calculations across many excel pages? For example, I have a page for each employee to detail their salary data each week and another page that sums all data from each employee. Can I have the sum page use the values from each of the other pages to calculate sums with??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use multiple pages (Office 97 suite)

    There are several ways to do this. It's best if the sheets you want to sum have the same layout.

    If you want a formula in one sheet to refer to a cell or a range of cells on another sheet, you can either type the reference in the form Sheet1!A1, or create it by clicking on the sheet tab of the other sheet first, and then selecting the desired cell or range. This only works when you are creating or editing a formula (starting with =).

    When you have sheet names with spaces in them, you must enclose the sheet name in single quotes if you type it yourself. Example: you want to add cell D3 from sheets named John B, Sue L and Emma C.

    ='John B'!D3+'Sue L'!D3+'Emma C'!D3

    You can also use a range of sheets in a formula. Example: you want to add the cells in C2:C5 for contiguous sheets John B through Amy W.

    =Sum('John B:Amy W'!C2:C5)

    HTH,
    Hans

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Use multiple pages (Office 97 suite)

    Hi,

    If I may just suggest a minor amendment to HansV's note:

    Insert a Front and a Back sheet in the workbook before the first and after the last working sheet and use these as your limits for the formulas. This avoids problems associated with inserting new sheets outside the range JohnB - AmyW - eg FredA, who would not be included in the Sum formula unless it was modified.

    Your formula would then be: =Sum('Front:Back'!C2:C5)

    HTH
    Peter Moran

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use multiple pages (Office 97 suite)

    Another method is to use the Consolidate Function on the Data menu.
    The help file is quite instructive, have a look.

    This does have the limitation though that if a new sheet is added (new employee) the formulas will need to be manually updated. The previous solution avoids this.

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Location
    Warrenton, Virginia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks to All

    Thanks to those who replied to my question. I assume by using the word "sheets" that I have to have all the pages on one spreadsheet as multiple sheets and not have a separate file for each one?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Thanks to All

    It's easiest to have multiple sheets in a single workbook file. But you can also refer to cells in other workbook files.

    When you are creating/editing a formula, you can activate another open workbook by clicking init (if it is visible), by using the Window menu or by typing Ctrl+F6. Next, you can select a worksheet and a cell or range.

    References to other workbooks look like

    =[Workbookname.xls]Sheetname!$B$10

    if the workbook referred to is open, and like

    ='C:Excel[Workbookname.xls]Sheetname'!$B$10

    if it is closed.

    Notes:
    (1) References to cells in other workbooks are absolute by default. You can change a reference to relative by selecting it and typing F4.
    (2) There is no way to refer to a series of external files, the way you can refer to a series of cells or worksheets within a workbook. So if you want to sum a cell across a series of workbooks, the formula gets cumbersome:

    ='C:Excel[Workbook1.xls]Sheet1'!$B$10+'C:Excel[Workbook2.xls]Sheet1'!$B$10+'C:Excel[Workbook3.xls]Sheet1'!$B$10

  7. #7
    New Lounger
    Join Date
    Aug 2002
    Location
    Warrenton, Virginia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks to All

    Thanks for the input - you told me just what I needed
    Tony

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks to All

    <<Control-F6>>

    Or ctrl-TAB, ctrl-shift-TAB to go backwards.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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