+ Reply to Thread
Results 1 to 3 of 3

Add the Content of a number of cells from multiple worksheet

  1. #1
    Mick.J
    Guest

    Add the Content of a number of cells from multiple worksheet


    I have a folder with a number of identical workbooks each one saved under a
    different department.

    Each workbook contains 13 worksheets identified as Wk1 to Wk13 representing
    13 weeks

    In week 13 I wish to identify in cell P5 the total of the contents of cell
    M5 in each of the 13 worksheets. I have tried several formulations but I
    either get an error message or it brings up the folder with all the workbooks
    as if I have hit the open icon.
    Each of the M5 cells will only have a number in it.

    Any help would be appreciated--
    with kind regards
    Mick

  2. #2
    JE McGimpsey
    Guest

    Re: Add the Content of a number of cells from multiple worksheet

    If I understand you correctly, you can use

    =SUM(Wk1:Wk13!M5)


    In article <[email protected]>,
    Mick.J <[email protected]> wrote:

    > I have a folder with a number of identical workbooks each one saved under a
    > different department.
    >
    > Each workbook contains 13 worksheets identified as Wk1 to Wk13 representing
    > 13 weeks
    >
    > In week 13 I wish to identify in cell P5 the total of the contents of cell
    > M5 in each of the 13 worksheets. I have tried several formulations but I
    > either get an error message or it brings up the folder with all the workbooks
    > as if I have hit the open icon.
    > Each of the M5 cells will only have a number in it.
    >
    > Any help would be appreciated--
    > with kind regards
    > Mick


  3. #3
    Bill Ridgeway
    Guest

    Re: Add the Content of a number of cells from multiple worksheet

    Mick,

    In simplest terms you would reference to only one cell. So P5 can only
    return the value of one cell. To do this -
    Place the cursor in P5
    Hit =
    Take the cursor to one of the worksheets
    Place the cursor in cell M5
    Hit <enter>

    That will return the value of cell M5 in worksheet x at cell P5 in worksheet
    y
    If the worksheet to which the formula refers is in the same spreadsheet the
    reference will change as the structure (layout) is changed. If, however,
    the worksheet to which the formula refers is NOT in the same spreadsheet the
    reference will NOT change as the structure (layout) is changed and the
    output will be incorrect.

    To get round this problem you should first name the target cells. To do
    this -
    Place the cursor in the target cell
    Click on <Insert><Name><Define>
    Type in a name (e.g.Total)
    Click <OK>
    and continue as above

    If you want the total of the 13 Cells P5 after hitting <enter> -
    insert = at the end of the formula and just go round again until all 13
    cells are included in the formula.

    Regards.

    Bill Ridgeway
    Computer Solutions

    "Mick.J" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have a folder with a number of identical workbooks each one saved under
    > a
    > different department.
    >
    > Each workbook contains 13 worksheets identified as Wk1 to Wk13
    > representing
    > 13 weeks
    >
    > In week 13 I wish to identify in cell P5 the total of the contents of cell
    > M5 in each of the 13 worksheets. I have tried several formulations but I
    > either get an error message or it brings up the folder with all the
    > workbooks
    > as if I have hit the open icon.
    > Each of the M5 cells will only have a number in it.
    >
    > Any help would be appreciated--
    > with kind regards
    > Mick




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1