+ Reply to Thread
Results 1 to 10 of 10

Collating from worksheets

  1. #1
    Mal
    Guest

    Collating from worksheets

    I have a large file that contains 28 different worksheets regarding
    individuals.
    On each worksheet is collated a number of sums from the data to give me
    info.

    Is it possible to have a 'summary' worksheet, listing the total of all the
    worksheets from the same cell on each worksheet on the summary sheet?
    I'm trying to save having to go through each worksheet and manually add the
    figures.

    Mal



  2. #2
    L. Howard Kittle
    Guest

    Re: Collating from worksheets

    Hi Mal,

    =SUM(Sheet1:Sheet28!F1)

    Will add all the F1's in sheet 1 to 28.

    HTH
    Regards,
    Howard

    "Mal" <[email protected]> wrote in message
    news:o%[email protected]...
    > I have a large file that contains 28 different worksheets regarding
    > individuals.
    > On each worksheet is collated a number of sums from the data to give me
    > info.
    >
    > Is it possible to have a 'summary' worksheet, listing the total of all the
    > worksheets from the same cell on each worksheet on the summary sheet?
    > I'm trying to save having to go through each worksheet and manually add
    > the figures.
    >
    > Mal
    >
    >




  3. #3
    Mal
    Guest

    Re: Collating from worksheets

    ermmm...it came up with name error!!

    Is it because my sheets are named not just sheet1 etc?
    Or is it because the cell i want to copy is a calculation?

    Mal

    "L. Howard Kittle" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Mal,
    >
    > =SUM(Sheet1:Sheet28!F1)
    >
    > Will add all the F1's in sheet 1 to 28.
    >
    > HTH
    > Regards,
    > Howard
    >
    > "Mal" <[email protected]> wrote in message
    > news:o%[email protected]...
    >> I have a large file that contains 28 different worksheets regarding
    >> individuals.
    >> On each worksheet is collated a number of sums from the data to give me
    >> info.
    >>
    >> Is it possible to have a 'summary' worksheet, listing the total of all
    >> the worksheets from the same cell on each worksheet on the summary sheet?
    >> I'm trying to save having to go through each worksheet and manually add
    >> the figures.
    >>
    >> Mal
    >>
    >>

    >
    >




  4. #4
    Mal
    Guest

    Re: Collating from worksheets

    Dont panic... i fiddled and fixed it!! Its the names!

    Mal

    "Mal" <[email protected]> wrote in message
    news:[email protected]...
    > ermmm...it came up with name error!!
    >
    > Is it because my sheets are named not just sheet1 etc?
    > Or is it because the cell i want to copy is a calculation?
    >
    > Mal
    >
    > "L. Howard Kittle" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi Mal,
    >>
    >> =SUM(Sheet1:Sheet28!F1)
    >>
    >> Will add all the F1's in sheet 1 to 28.
    >>
    >> HTH
    >> Regards,
    >> Howard
    >>
    >> "Mal" <[email protected]> wrote in message
    >> news:o%[email protected]...
    >>> I have a large file that contains 28 different worksheets regarding
    >>> individuals.
    >>> On each worksheet is collated a number of sums from the data to give me
    >>> info.
    >>>
    >>> Is it possible to have a 'summary' worksheet, listing the total of all
    >>> the worksheets from the same cell on each worksheet on the summary
    >>> sheet?
    >>> I'm trying to save having to go through each worksheet and manually add
    >>> the figures.
    >>>
    >>> Mal
    >>>
    >>>

    >>
    >>

    >
    >




  5. #5
    L. Howard Kittle
    Guest

    Re: Collating from worksheets

    Hi Mal,

    Yes, it must be the actual name of the sheets. In this example the sheet
    named Mice is the first sheet and Cats is the 28th sheet.

    =SUM(Mice:Cats!F1)

    A quick way to make this formula is to type =SUM( into the cell you want the
    totals in and then select the first sheet and with the shift key held down
    select the last sheet. All the sheet tabs should be highlighted. Release
    the shift key and click on the cell you want to sum. Now add the ) to the
    formula and hit enter. This assumes you are totaling 28 sheets and the
    formula is on a sheet other than the 28. 29 sheets in all.

    If you are totaling on the first of 28 sheets type in =SUM( and hold down
    the shift key and select the last sheet and then the cell and then the ) and
    then enter.

    HTH
    Regards,
    Howard

    "Mal" <[email protected]> wrote in message
    news:o%[email protected]...
    > I have a large file that contains 28 different worksheets regarding
    > individuals.
    > On each worksheet is collated a number of sums from the data to give me
    > info.
    >
    > Is it possible to have a 'summary' worksheet, listing the total of all the
    > worksheets from the same cell on each worksheet on the summary sheet?
    > I'm trying to save having to go through each worksheet and manually add
    > the figures.
    >
    > Mal
    >
    >




  6. #6
    Dave Peterson
    Guest

    Re: Collating from worksheets

    I like this technique...

    Create two new worksheets--one to the far right and one to the far left.

    Call them Start and End

    Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

    =sum(start:end!B37)

    Then you can drag sheets in and out of that sandwich to play what if games.

    I'd put a couple of notes on each of these sheets:

    "don't delete this sheet!"

    And protect the worksheet so that people don't use it for real data.

    Mal wrote:
    >
    > I have a large file that contains 28 different worksheets regarding
    > individuals.
    > On each worksheet is collated a number of sums from the data to give me
    > info.
    >
    > Is it possible to have a 'summary' worksheet, listing the total of all the
    > worksheets from the same cell on each worksheet on the summary sheet?
    > I'm trying to save having to go through each worksheet and manually add the
    > figures.
    >
    > Mal


    --

    Dave Peterson

  7. #7
    Mal
    Guest

    Re: Collating from worksheets

    How can i drag a sheet??

    Thanks to both for your help.

    Mal

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    >I like this technique...
    >
    > Create two new worksheets--one to the far right and one to the far left.
    >
    > Call them Start and End
    >
    > Then using a sheet (Summary) that is outside this "sandwich" of
    > worksheets:
    >
    > =sum(start:end!B37)
    >
    > Then you can drag sheets in and out of that sandwich to play what if
    > games.
    >
    > I'd put a couple of notes on each of these sheets:
    >
    > "don't delete this sheet!"
    >
    > And protect the worksheet so that people don't use it for real data.
    >
    > Mal wrote:
    >>
    >> I have a large file that contains 28 different worksheets regarding
    >> individuals.
    >> On each worksheet is collated a number of sums from the data to give me
    >> info.
    >>
    >> Is it possible to have a 'summary' worksheet, listing the total of all
    >> the
    >> worksheets from the same cell on each worksheet on the summary sheet?
    >> I'm trying to save having to go through each worksheet and manually add
    >> the
    >> figures.
    >>
    >> Mal

    >
    > --
    >
    > Dave Peterson




  8. #8
    Roger Govier
    Guest

    Re: Collating from worksheets

    Hi Mal

    Click on sheet tab.
    Hold left mouse button down.
    Drag to required location.
    Release.

    Regards

    Roger Govier


    Mal wrote:
    > How can i drag a sheet??
    >
    > Thanks to both for your help.
    >
    > Mal
    >
    > "Dave Peterson" <[email protected]> wrote in message
    > news:[email protected]...
    >
    >>I like this technique...
    >>
    >>Create two new worksheets--one to the far right and one to the far left.
    >>
    >>Call them Start and End
    >>
    >>Then using a sheet (Summary) that is outside this "sandwich" of
    >>worksheets:
    >>
    >>=sum(start:end!B37)
    >>
    >>Then you can drag sheets in and out of that sandwich to play what if
    >>games.
    >>
    >>I'd put a couple of notes on each of these sheets:
    >>
    >>"don't delete this sheet!"
    >>
    >>And protect the worksheet so that people don't use it for real data.
    >>
    >>Mal wrote:
    >>
    >>> I have a large file that contains 28 different worksheets regarding
    >>>individuals.
    >>>On each worksheet is collated a number of sums from the data to give me
    >>>info.
    >>>
    >>>Is it possible to have a 'summary' worksheet, listing the total of all
    >>>the
    >>>worksheets from the same cell on each worksheet on the summary sheet?
    >>>I'm trying to save having to go through each worksheet and manually add
    >>>the
    >>>figures.
    >>>
    >>>Mal

    >>
    >>--
    >>
    >>Dave Peterson

    >
    >
    >


  9. #9
    Mal
    Guest

    Re: Collating from worksheets

    Sometimes its soooooo. simple!!!

    Thanks.....

    Mal

    "Roger Govier" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Mal
    >
    > Click on sheet tab.
    > Hold left mouse button down.
    > Drag to required location.
    > Release.
    >
    > Regards
    >
    > Roger Govier
    >
    >
    > Mal wrote:
    >> How can i drag a sheet??
    >>
    >> Thanks to both for your help.
    >>
    >> Mal
    >>
    >> "Dave Peterson" <[email protected]> wrote in message
    >> news:[email protected]...
    >>
    >>>I like this technique...
    >>>
    >>>Create two new worksheets--one to the far right and one to the far left.
    >>>
    >>>Call them Start and End
    >>>
    >>>Then using a sheet (Summary) that is outside this "sandwich" of
    >>>worksheets:
    >>>
    >>>=sum(start:end!B37)
    >>>
    >>>Then you can drag sheets in and out of that sandwich to play what if
    >>>games.
    >>>
    >>>I'd put a couple of notes on each of these sheets:
    >>>
    >>>"don't delete this sheet!"
    >>>
    >>>And protect the worksheet so that people don't use it for real data.
    >>>
    >>>Mal wrote:
    >>>
    >>>> I have a large file that contains 28 different worksheets regarding
    >>>>individuals.
    >>>>On each worksheet is collated a number of sums from the data to give me
    >>>>info.
    >>>>
    >>>>Is it possible to have a 'summary' worksheet, listing the total of all
    >>>>the
    >>>>worksheets from the same cell on each worksheet on the summary sheet?
    >>>>I'm trying to save having to go through each worksheet and manually add
    >>>>the
    >>>>figures.
    >>>>
    >>>>Mal
    >>>
    >>>--
    >>>
    >>>Dave Peterson

    >>
    >>



  10. #10
    Ron de Bruin
    Guest

    Re: Collating from worksheets

    Tip: When you press the Ctrl key when you do this you create a copy of the sheet

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Mal" <[email protected]> wrote in message news:[email protected]...
    > Sometimes its soooooo. simple!!!
    >
    > Thanks.....
    >
    > Mal
    >
    > "Roger Govier" <[email protected]> wrote in message news:[email protected]...
    >> Hi Mal
    >>
    >> Click on sheet tab.
    >> Hold left mouse button down.
    >> Drag to required location.
    >> Release.
    >>
    >> Regards
    >>
    >> Roger Govier
    >>
    >>
    >> Mal wrote:
    >>> How can i drag a sheet??
    >>>
    >>> Thanks to both for your help.
    >>>
    >>> Mal
    >>>
    >>> "Dave Peterson" <[email protected]> wrote in message news:[email protected]...
    >>>
    >>>>I like this technique...
    >>>>
    >>>>Create two new worksheets--one to the far right and one to the far left.
    >>>>
    >>>>Call them Start and End
    >>>>
    >>>>Then using a sheet (Summary) that is outside this "sandwich" of worksheets:
    >>>>
    >>>>=sum(start:end!B37)
    >>>>
    >>>>Then you can drag sheets in and out of that sandwich to play what if games.
    >>>>
    >>>>I'd put a couple of notes on each of these sheets:
    >>>>
    >>>>"don't delete this sheet!"
    >>>>
    >>>>And protect the worksheet so that people don't use it for real data.
    >>>>
    >>>>Mal wrote:
    >>>>
    >>>>> I have a large file that contains 28 different worksheets regarding
    >>>>>individuals.
    >>>>>On each worksheet is collated a number of sums from the data to give me
    >>>>>info.
    >>>>>
    >>>>>Is it possible to have a 'summary' worksheet, listing the total of all the
    >>>>>worksheets from the same cell on each worksheet on the summary sheet?
    >>>>>I'm trying to save having to go through each worksheet and manually add the
    >>>>>figures.
    >>>>>
    >>>>>Mal
    >>>>
    >>>>--
    >>>>
    >>>>Dave Peterson
    >>>
    >>>

    >




+ 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