consolidate numbers by position from sheets in different workbook.

  • Thread starter Thread starter Rocketman
  • Start date Start date
R

Rocketman

How to use the consolidate command to add the contents of the cells on 120
different sheets in different workbooks into an identical cell position on a
summary sheet. The online Excell help is not sufficiently specific to
describe how to do this. Is there a good online Excell tutorial available or
an Exell Example Book that has examples.
 
Basics are as given in the help (copied at the end of this post)

You have to figure out a way to pass the cell references you need to
consolidate.
eg if you have three workbooks then use thi
=SUM([Workbook1]Sheet1:Sheet10!B3,[Workbook2]Sheet1:Sheet10!B3,[Workbook3]Sheet1:Sheet10!B3)

Ideally you should use a macro...

'-------------FROM HELP------------
On the consolidation worksheet, copy or enter the labels you want for the
consolidated data.
Click a cell that you want to contain consolidated data.
Type a formula that includes references to the source cells on each
worksheet that contains data you want to consolidate.
For example, to combine the data in cell B3 from worksheets Sheet 2 through
Sheet 7 inclusive, you could type =SUM(Sheet2:Sheet7!B3). If the data to
consolidate is in different cells on different worksheets, enter a formula
such as this: =SUM(Sheet3!B4, Sheet4!A7, Sheet5!C5). To enter a reference
such as Sheet3!B4 in a formula without typing, type the formula up to the
point where you need the reference, click the worksheet tab, and then click
the cell.
 
I wish to Thank Sheeloo for giving me a very good response. I am not sure of
the protocol of posting appreciation,but I will respond until told otherwise.
Aloha!Rocketman

Sheeloo said:
Basics are as given in the help (copied at the end of this post)

You have to figure out a way to pass the cell references you need to
consolidate.
eg if you have three workbooks then use this
=SUM([Workbook1]Sheet1:Sheet10!B3,[Workbook2]Sheet1:Sheet10!B3,[Workbook3]Sheet1:Sheet10!B3)

Ideally you should use a macro...

'-------------FROM HELP------------
On the consolidation worksheet, copy or enter the labels you want for the
consolidated data.
Click a cell that you want to contain consolidated data.
Type a formula that includes references to the source cells on each
worksheet that contains data you want to consolidate.
For example, to combine the data in cell B3 from worksheets Sheet 2 through
Sheet 7 inclusive, you could type =SUM(Sheet2:Sheet7!B3). If the data to
consolidate is in different cells on different worksheets, enter a formula
such as this: =SUM(Sheet3!B4, Sheet4!A7, Sheet5!C5). To enter a reference
such as Sheet3!B4 in a formula without typing, type the formula up to the
point where you need the reference, click the worksheet tab, and then click
the cell.



Rocketman said:
How to use the consolidate command to add the contents of the cells on 120
different sheets in different workbooks into an identical cell position on a
summary sheet. The online Excell help is not sufficiently specific to
describe how to do this. Is there a good online Excell tutorial available or
an Exell Example Book that has examples.
Click to expand...
Click to expand...
 
Back
Top