Consolidating Excel sheets within Office Binder in Windows 2000
 

I want to (yearly) consolidate about 20 worksheets (from within workbooks)
that are saved in a binder.
This is possible by entering one after another the Reference for each sheet
in the Data Consolidation dialog box, but requires careful use of ' and []
and ! signs to separate the cell_range from the sheet name from the workbook
name from the path.
Is there an easier way to specify the Reference ranges?
(Picking them from within the Binder would be perfect, but that doesn't work).

Consolidating Excel sheets within Office Binder in Windows 2000
 

Have you thought of using edit-copy move sheet- create copy to a common
summary book from each workbook

"Felix" wrote:

I want to (yearly) consolidate about 20 worksheets (from within workbooks)
that are saved in a binder.
This is possible by entering one after another the Reference for each sheet
in the Data Consolidation dialog box, but requires careful use of ' and []
and ! signs to separate the cell_range from the sheet name from the workbook
name from the path.
Is there an easier way to specify the Reference ranges?
(Picking them from within the Binder would be perfect, but that doesn't work).

Consolidating Excel sheets within Office Binder in Windows 200
 

Good suggestion. (SAhould have thought about it myself, but sometimes you
develop a blind spot if you look too long at the same thing). I will have to
copy the range of cells from each workbook I receive back into the
consolidation workbook, but this will be less error prone.
thanks fro yoru very quick reply!
--
Think, then move


"bj" wrote:

Have you thought of using edit-copy move sheet- create copy to a common
summary book from each workbook

"Felix" wrote:

I want to (yearly) consolidate about 20 worksheets (from within workbooks)
that are saved in a binder.
This is possible by entering one after another the Reference for each sheet
in the Data Consolidation dialog box, but requires careful use of ' and []
and ! signs to separate the cell_range from the sheet name from the workbook
name from the path.
Is there an easier way to specify the Reference ranges?
(Picking them from within the Binder would be perfect, but that doesn't work).

Consolidating Excel sheets within Office Binder in Windows 200
 

BJ,

Fyi, there is 1 disadvantage in this method, i.e. when you expand the
consolidated data to see the underlying detail lines.
If you select the data range from different workbooks, then the name of the
workbook in the expanded detail line gives an indication of the company it
came from (as they save the filled in template under their company name).
However, if the data were first copied into the same workbook on different
sheets (named according to the company), it only mentions the name of the
workbook each time again for each detail line, but not the name of the sheet.
This makes the expand feature rather irrelevant. Do you knwo a solution to
that too?
--
Think, then move


"bj" wrote:

Have you thought of using edit-copy move sheet- create copy to a common
summary book from each workbook

"Felix" wrote:

I want to (yearly) consolidate about 20 worksheets (from within workbooks)
that are saved in a binder.
This is possible by entering one after another the Reference for each sheet
in the Data Consolidation dialog box, but requires careful use of ' and []
and ! signs to separate the cell_range from the sheet name from the workbook
name from the path.
Is there an easier way to specify the Reference ranges?
(Picking them from within the Binder would be perfect, but that doesn't work).