I don't recall there being an Excel limit on the number of consolidation
ranges.
If you're using DataConsolidate and the the workbooks and ranges are always
in flux (adding/deleting workbooks, changing data ranges, etc) be aware of
the maintenance issues. If the model becomes unwieldy, it's time to either
automate the maintenance or find another solution. Perhaps the Template
Wizard with Data Tracking.

***********
Regards,
Ron


"steve" wrote:

Thanks Ron. I got that to work (after a fashion!) Many thanks for the
help. Do you know if there is a limit to the number of workbooks one can
consolidate into one ?

Rgds
Steve

"Ron Coderre" wrote:

If the data ranges have only one row of column headings and only one column
of row headings....check out DataConsolidation.

But...if there are multiple columns of row headings (and only one row of
column headings)...You might be able to use MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts:

In my example, ranges named rngNameAmt1 (the ranges contain 2 columns: Name
and Amount) are consolidated from 4 Excel workbooks into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
---Col headings (PmtAmt, DatePaid, etc),
---Col Headings in different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in Named Ranges.
---You may use the same range name in different wkbks.

(Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
---Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

(Note: you can create the above SQL codes in Excel by using formulas and
just paste the sQL range into MS Query's SQL window.)

Return the data to Excel.

Once that is done....all you need to do to get the latest data is click in
the data range then DataRefresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with?

Ron


"steve" wrote:

Hi
I'm using excel 2002. I need to import data that is held by several people
in their own excel workbooks and put their data onto one excel worksheet so I
have a summary of everyones data. We will all have the same columns and rows
etc.

I tried the simple copy/ paste and copy and move functions but there are
some formulae in the sheets that were lost during the move.

I have tried 'data import' route as suggested by others, but when i come to
import the second person's data the 'import data' is greyed out if I want to
put the data below the first set. If I go to put it alongside the first one
it doesn't grey out and I can import it there. However I want to put it below
the first data!

Anyone any ideas how I can put the second set of data below the first, and
obviously subsequent data below the second etc (and why it should grey out?)

Many thanks