+ Reply to Thread
Results 1 to 6 of 6

Dynamic summary sheet for a workbook

  1. #1

    Dynamic summary sheet for a workbook

    Hi, I have been searching around here for an example of how I could
    add a summary sheet to an existing workbook that would pull specific
    data from each of those other sheets. The workbook is constantly
    expanding and the summary would need to be updated with every entry. I
    have found many good examples of things that are close... but so far
    nothing specific.

    Any suggestions/thoughts?


  2. #2
    Don Guillett
    Guest

    Re: Dynamic summary sheet for a workbook

    One way to do a summary is to put the value from each worksheet in the SAME
    place on the worksheet and use
    =sum(firstsheet:lastsheet!a1)

    --
    Don Guillett
    SalesAid Software
    [email protected]
    <[email protected]> wrote in message
    news:[email protected]...
    > Hi, I have been searching around here for an example of how I could
    > add a summary sheet to an existing workbook that would pull specific
    > data from each of those other sheets. The workbook is constantly
    > expanding and the summary would need to be updated with every entry. I
    > have found many good examples of things that are close... but so far
    > nothing specific.
    >
    > Any suggestions/thoughts?
    >




  3. #3

    Re: Dynamic summary sheet for a workbook

    Thanks Don. I should have better explained that I'd like the summary
    sheet to show specific values from each sheet rather than a SUM across
    the sheets.

    There is a cell on each sheet for Name, Location, Cost1, Cost2 that are
    updated by information on that specific sheet. The sheets themselves
    are named by (Last Name,First Initial). Whenever we need to enter a
    new person into the ystem we create a new sheet for them (which is
    sorted alphabetically). I am looking for a summary sheet that would
    list all of the names in column 1, the locations in column 2, and the
    costs in columns 3 and 4 (that would be able to be updated and sorted
    whenever a new sheet is added).

    This seems to be beyond my current knowledge

    Thanks again for any help.


  4. #4
    Tom Ogilvy
    Guest

    Re: Dynamic summary sheet for a workbook

    Assume the data sheets are contiguous in the tab order

    Before the first data sheet insert a sheet and name it First
    Directly after the last data sheet insert a sheet and name it Last

    Anywhere outside these sheet insert a sheet and name it summary

    in sheet summary use 3D formulas like

    =Sum(First:Last!A1)

    Always insert your new data sheets between First and Last

    --
    Regards,
    Tom Ogilvy




    <[email protected]> wrote in message
    news:[email protected]...
    > Hi, I have been searching around here for an example of how I could
    > add a summary sheet to an existing workbook that would pull specific
    > data from each of those other sheets. The workbook is constantly
    > expanding and the summary would need to be updated with every entry. I
    > have found many good examples of things that are close... but so far
    > nothing specific.
    >
    > Any suggestions/thoughts?
    >




  5. #5
    Tom Ogilvy
    Guest

    Re: Dynamic summary sheet for a workbook

    When you add a sheet is it too much to go to the summary sheet, insert a
    row, enter the name and drag down the formula from the row above? Or even
    enter it on the next blank row, then resort

    you could use formulas like (in B2 for example)

    =Indirect("'" & A2 & "'!B35")
    ( in C2 for example)
    =Indirect("'" & A2 & "'!F37")

    and so forth.

    The B35 and F37 examples will not be changed when copied down or sorted.

    If you enter at the bottom, then sort on Column A.

    Seems like a pretty minimal requirement.

    --
    Regards,
    Tom Ogilvy


    <[email protected]> wrote in message
    news:[email protected]...
    > Thanks Don. I should have better explained that I'd like the summary
    > sheet to show specific values from each sheet rather than a SUM across
    > the sheets.
    >
    > There is a cell on each sheet for Name, Location, Cost1, Cost2 that are
    > updated by information on that specific sheet. The sheets themselves
    > are named by (Last Name,First Initial). Whenever we need to enter a
    > new person into the ystem we create a new sheet for them (which is
    > sorted alphabetically). I am looking for a summary sheet that would
    > list all of the names in column 1, the locations in column 2, and the
    > costs in columns 3 and 4 (that would be able to be updated and sorted
    > whenever a new sheet is added).
    >
    > This seems to be beyond my current knowledge
    >
    > Thanks again for any help.
    >




  6. #6
    Registered User
    Join Date
    08-21-2005
    Posts
    8

    Summary Sheet & Main Sheet dynamic interaction

    [email protected]

    I have an application I developed that selects data from a summary sheet row and populates it to a main sheet. Option to write-back to summary sheet with changes as a new row
    Cannot upload Excel Workbooks, but please do contact my E Mail if you would like a copy.
    NOT FOR SALE

+ 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