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?

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)

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?

> 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?

> 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

 Page 1 of 1 [ 6 post ]

Relevant Pages