HELP! Summarize sheet data on one sheet
Author 
Message 
AB #1 / 6

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 


Dick Kusleik #2 / 6

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 


AB #3 / 6

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 Zaxis to Yaxis instead of Xaxis to Yaxis. 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 


Quentin Clea #4 / 6

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 


Dick Kusleik #5 / 6

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 Zaxis to Yaxis instead of Xaxis > to Yaxis. 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.
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 


Roel #6 / 6

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 datasheets follow behind your total sheet. (otherwise it will not sum) Goodluck Roel

Wed, 18 Jun 1902 08:00:00 GMT 


