This might be an option for you if:
a)You are familiar with MS Query, and
b)Are OK with using (or learning) some basic SQL code:
Assumptions:
-The data in each wkbk is in named ranges
1)Select the cell where you want the consolidated data to start
2)Data>Import External Data>New Database Query
Databases: Excel Files
-Select one of the files, pick the data range and columns to import and any
criteria.
-Select Edit the Query
-Click the SQL button
-Replace the displayed SQL code with an adapted version of this:
SELECT Name, Amount From (SELECT Name, Amount
FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk UNION SELECT Name, Amount
FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk UNION SELECT Name, Amount
FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk UNION SELECT Name, Amount
FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk)
Return the data to Excel.
The above will consolidate ranges named rngNameAmt1 (which contain 2
columns: Name and Amount) from 4 Excel files into the one workbook
One that is done....all you need to do to get the latest data is click in
the data range then Dave>Refresh Data.
Is that something you could play with?
