Home >  BI for Excel >

Consolidating Excel Data
From Many Workbooks

Bruce,

When I read your note this morning I wasn't sure how to respond. Your problem is a classic one for Excel. It's the cause of a lot of Spreadsheet Hell.

One potential approach would be to write a complicated macro that consolidates those workbooks. But that would be expensive to create, and maintain. Worse, the macro probably would break continually because my impression is that you're consolidating data from many different kinds of workbooks, with a lot of user interaction. 

But then I got a call from a friend. He said that his firm had just closed the sale for an Excel-friendly OLAP solution to a company with more than 300 locations. The company will regularly consolidate Excel-sourced data using that program. Interestingly, the product that the company chose happens to be the least expensive of the three Excel-friendly OLAPs. It's what I recommend to small and medium businesses.

I think that general approach is the way for you to go. At the front end, use Excel to gather your data. At the back end, use Excel to report and analyze your data. But in the middle, use an Excel-friendly OLAP product to consolidate and manage your data. Unlike any other database technology I know of, it allows spreadsheet formulas to return values to cells.

Several days ago I posted What Do Users Need From Excel-Friendly Data Sources?, which describes the general approach. Particularly, read the last section titled "Write-Back from Excel".

Hope this helps,

Charley Kyd

(Email Comments)