You might be able to use MS Query to consolidate Excel ranges from your
multiple wkshts, as long as you don't mind formulas being converted to
static values in the consolidation area.
This example uses 5 named ranges which can be in the same or different
worksheets of the same workbook.
Assumptions:
The data in each wksht is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price, ...etc)
--->Columns are in the same order.
The data in each wksht must be in named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
--->You can re-use the same range name if it is in different wkbks.
Save the wkbk after creating the names so Excel can find it with the names.
(Note: In the next steps, 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)Data>Import External Data>New Database Query
Browse to one of The files, pick ONE data range 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 * FROM `C:\MyWkbk`.rng1111Data
UNION ALL
SELECT * FROM `C:\MyWkbk`.rng2222Data
UNION ALL
SELECT * FROM `C:\MyWkbk`.rng3333Data
UNION ALL
SELECT * FROM `C:\MyWkbk`.rng4444Data
UNION ALL
SELECT * FROM `C:\MyWkbk`.rng5555Data
(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.
Once that is done....to get the latest data just click in the data range
then Data>Refresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro