Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
consolidate numbers by position from sheets in different workbook.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
consolidate numbers by position from sheets in different workbook.
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
consolidate numbers by position from sheets in different workb
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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I consolidate many sheets in same file to 1? | Excel Discussion (Misc queries) | |||
consolidate over 50 sheets by Macro | Excel Discussion (Misc queries) | |||
how to consolidate data in different excel sheets | Excel Worksheet Functions | |||
Consolidate different sheets to different workbooks | Excel Worksheet Functions | |||
Consolidate sheets | Excel Worksheet Functions |