HELP! Summarize sheet data on one sheet 
Author Message
 HELP! Summarize sheet data on one sheet

Hello Group:

I have a Workbook with 40+ Worksheets, and each sheet is an identical
data sheet, but with different data.

How can I summarize all this data onto a new worksheet in the workbook:

In the first column will be all the data from every sheet's cell B3,

In the next column will be all the data from every sheet's cell J9,

In the next column will be all the data from every sheet's cell M7,
etc...

The sheets are numbered sequentially, like 2A1, 2A2, 2A3, etc.

Is there any way to copy and paste, but copy from sequential sheets,
then paste into a column?  Do I really need to revert to VBA, or is
there perhaps a function which will fill my needs?

Many Thanks in advance!

A.B.



Wed, 18 Jun 1902 08:00:00 GMT  
 HELP! Summarize sheet data on one sheet
You can use a formula like this

=SUM(Sheet1:Sheet3!A1)

Just substitute your sheet names

HTH
{*filter*} K.

Quote:

> Hello Group:

> I have a Workbook with 40+ Worksheets, and each sheet is an identical
> data sheet, but with different data.

> How can I summarize all this data onto a new worksheet in the workbook:

> In the first column will be all the data from every sheet's cell B3,

> In the next column will be all the data from every sheet's cell J9,

> In the next column will be all the data from every sheet's cell M7,
> etc...

> The sheets are numbered sequentially, like 2A1, 2A2, 2A3, etc.

> Is there any way to copy and paste, but copy from sequential sheets,
> then paste into a column?  Do I really need to revert to VBA, or is
> there perhaps a function which will fill my needs?

> Many Thanks in advance!

> A.B.



Wed, 18 Jun 1902 08:00:00 GMT  
 HELP! Summarize sheet data on one sheet

Hi{*filter*}:

I tried this, and it _adds up_ all the A1 values and returns a single
value.

What I need to do is make a _vertical list_ of the A1 values, like as if
the TRANSPOSE function could go from Z-axis to Y-axis instead of X-axis
to Y-axis.  So if each sheet contains in the "name" cell:

Sheet1!A1 contains Tom
Sheet2!A1 contains{*filter*}
Sheet3!A1 contains Harry
...

...then I need to make a list on the new Summary sheet so Column 1 is:

Tom
{*filter*}
Harry
...

I guess I should explain that each sheet in the workbook is a "form"
with data from an individual test.  So each "form" has sample name, and
about six other facts about the sample, each fact at a fixed cell
location, the same location on all the "forms" (sheets).  What I need to
do is to make a sheet which summarizes all these data items on a single
new sheet.

Any help will be much appreciated.  There must be a function which can
do this easily, but I'm stumped!!

AB

Quote:

> You can use a formula like this

> =SUM(Sheet1:Sheet3!A1)

> Just substitute your sheet names

> HTH
>{*filter*} K.


> > Hello Group:

> > I have a Workbook with 40+ Worksheets, and each sheet is an identical
> > data sheet, but with different data.

> > How can I summarize all this data onto a new worksheet in the workbook:

> > In the first column will be all the data from every sheet's cell B3,

> > In the next column will be all the data from every sheet's cell J9,

> > In the next column will be all the data from every sheet's cell M7,
> > etc...

> > The sheets are numbered sequentially, like 2A1, 2A2, 2A3, etc.

> > Is there any way to copy and paste, but copy from sequential sheets,
> > then paste into a column?  Do I really need to revert to VBA, or is
> > there perhaps a function which will fill my needs?

> > Many Thanks in advance!

> > A.B.



Wed, 18 Jun 1902 08:00:00 GMT  
 HELP! Summarize sheet data on one sheet
You have the perfect setup for a Group Edit.  On your new
"summary" worksheet try entering a formula as follows

=SUM(

click on the first of your 40+ sheets and select cell B3
click on the last of your 40+ sheets and select cell B3

close the parenthesis.

This nonchalantly assumes your 40+ sheets are all arranged
contiguously in the workbook!

 > Hello Group:

 > I have a Workbook with 40+ Worksheets, and each sheet is an
 > identical data sheet, but with different data.

 > How can I summarize all this data onto a new worksheet in the
 > workbook:

 > In the first column will be all the data from every sheet's cell
 > B3,

 > In the next column will be all the data from every sheet's cell J9,

 > In the next column will be all the data from every sheet's cell M7,
 > etc...

 > The sheets are numbered sequentially, like 2A1, 2A2, 2A3, etc.

 > Is there any way to copy and paste, but copy from sequential
 > sheets, then paste into a column?  Do I really need to revert to
 > VBA, or is there perhaps a function which will fill my needs?

 > Many Thanks in advance!

 > A.B.

--
Quentin Cleal
Leeds, UK



Sat, 08 Mar 2003 07:14:09 GMT  
 HELP! Summarize sheet data on one sheet
I don't know of any easy way, so I'll give you the not so easy way.

In a spare column, put your sheet names.  In a spare row, put a text
representation of the cell you want to reference on the other sheets.  I
might look like this:

                 A1         A10
Sheet2
Sheet3

Then use this formula

=INDIRECT($A2&"!"&B$1)

Assumes your sheet names are in column A and cell reference text is in row
1.  You can hide this column and row once you get it set up.

HTH
{*filter*} K.

Quote:

> Hi{*filter*}:

> I tried this, and it _adds up_ all the A1 values and returns a single
> value.

> What I need to do is make a _vertical list_ of the A1 values, like as if
> the TRANSPOSE function could go from Z-axis to Y-axis instead of X-axis
> to Y-axis.  So if each sheet contains in the "name" cell:

> Sheet1!A1 contains Tom
> Sheet2!A1 contains{*filter*}
> Sheet3!A1 contains Harry
> ...

> ...then I need to make a list on the new Summary sheet so Column 1 is:

> Tom
>{*filter*}
> Harry
> ...

> I guess I should explain that each sheet in the workbook is a "form"
> with data from an individual test.  So each "form" has sample name, and
> about six other facts about the sample, each fact at a fixed cell
> location, the same location on all the "forms" (sheets).  What I need to
> do is to make a sheet which summarizes all these data items on a single
> new sheet.

> Any help will be much appreciated.  There must be a function which can
> do this easily, but I'm stumped!!

> AB


> > You can use a formula like this

> > =SUM(Sheet1:Sheet3!A1)

> > Just substitute your sheet names

> > HTH
> >{*filter*} K.




- Show quoted text -

Quote:

> > > Hello Group:

> > > I have a Workbook with 40+ Worksheets, and each sheet is an identical
> > > data sheet, but with different data.

> > > How can I summarize all this data onto a new worksheet in the
workbook:

> > > In the first column will be all the data from every sheet's cell B3,

> > > In the next column will be all the data from every sheet's cell J9,

> > > In the next column will be all the data from every sheet's cell M7,
> > > etc...

> > > The sheets are numbered sequentially, like 2A1, 2A2, 2A3, etc.

> > > Is there any way to copy and paste, but copy from sequential sheets,
> > > then paste into a column?  Do I really need to revert to VBA, or is
> > > there perhaps a function which will fill my needs?

> > > Many Thanks in advance!

> > > A.B.



Wed, 18 Jun 1902 08:00:00 GMT  
 HELP! Summarize sheet data on one sheet



Quote:

> Hello Group:

> I have a Workbook with 40+ Worksheets, and each sheet is an identical
> data sheet, but with different data.

> How can I summarize all this data onto a new worksheet in the workbook:

> In the first column will be all the data from every sheet's cell B3,

> In the next column will be all the data from every sheet's cell J9,

> In the next column will be all the data from every sheet's cell M7,
> etc...

> The sheets are numbered sequentially, like 2A1, 2A2, 2A3, etc.

> Is there any way to copy and paste, but copy from sequential sheets,
> then paste into a column?  Do I really need to revert to VBA, or is
> there perhaps a function which will fill my needs?

You can use this formula in your total sheet:

=sum(2A1:2A3!B3)

it also works on ranges on cells (i.e. =sum(2A1:2A3!B3:B6)

Make sure all data-sheets follow behind your total sheet. (otherwise it will
not sum)

Goodluck
Roel



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. How to use data fron one sheet to print forms created in another sheet

2. automaticly copying data from one sheet into another sheet using VBA

3. Inserting data on one sheet from another sheet

4. formulas on one sheet affecting data on other sheets

5. using data from one sheet to populate multiple sheets

6. Summarizing data on different sheets

7. Summarizing data of several sheets

8. How to automate summarizing data from many sheets

9. Summarizing Data From Several Sheets (2000)

10. Checking the row data of First Sheet and deleting the related data from the second sheet


 
Powered by phpBB® Forum Software © phpBB Group