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).

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).

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).

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).