Hello to All:
I am trying to take 36 Workbooks with the same categories of data and
combine into 1 Workbook without copying/pasting each workbook. I evidently
am doing something wrong using the Data - Consolidate function. Any help
would be greatly appreciated.
What error messages are you getting?
Have you followed the step by step help - see below copied from
Excel's help?
If not please describe the steps you go through.
HTH
About consolidating data in multiple worksheets
Show All
Hide All
To summarize and report results from multiple worksheets, you can
consolidate data from each worksheet into a master worksheet. The
worksheets can be in the same workbook or other workbooks. When you
consolidate data, you are assembling data so you can more easily
update and aggregate it on a regular or ad hoc basis.
For example, if you have a worksheet of expense figures for each of
your regional offices, you might use a consolidation to roll up these
figures into a corporate expense worksheet. This master worksheet
might contain sales totals and averages, current inventory levels, and
highest selling products for the entire enterprise.
To consolidate data, you use the Consolidate command from the Data
menu to display the Consolidate dialog box. You can use this dialog
box in several ways to consolidate your data:
Position Use this approach when the data in all worksheets is
arranged in identical order and location.
Category Use this approach when each worksheet organizes the data
differently, but has the same row and column labels, which you can use
to match the data.
3-D formulas Use this approach when the worksheets do not have a
consistent pattern you can rely on. You can create formulas that refer
to cells in each range of data that you're combining. Formulas that
refer to cells on multiple worksheets are called 3-D formulas.
Consolidate by position
Consolidate by position when the data in all source areas is arranged
in identical order and location; for example, if you have data from a
series of worksheets that were created from the same template, you can
consolidate the data by position.
You can set up the consolidation to update automatically when the
source data changes, but you won't be able to change which cells and
ranges are included in the consolidation. Or you can update the
consolidation manually, allowing you to change the included cells and
ranges.
Consolidate by category
Consolidate by category when you want to summarize a set of worksheets
that have the same row and column labels but organize the data
differently. This method combines data that has matching labels from
each worksheet.
You can set up the consolidation to update automatically when the
source data changes, but you won't be able to change which cells and
ranges are included in the consolidation. Alternatively, you can
update the consolidation manually, allowing you to change the included
cells and ranges.
Consolidate by using 3-D formulas
When you use 3-D references (3-D reference: A reference to a range
that spans two or more worksheets in a workbook.) in formulas, there
are no restrictions on the layout of the separate ranges of data. You
can change the consolidation any way you need to. The consolidation
updates automatically when the data in the source ranges changes.
Use formulas to combine data In the following example, the formula
in cell A2 adds three numbers that are located in different positions
on three different worksheets.
Add data to a consolidation with 3-D references When all of your
source worksheets have the same layout, you can use a range of sheet
names in 3-D formulas. To add another worksheet to the consolidation,
just move the sheet into the range your formula refers to.
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
