I have a customer who is currently using Excel to do their staff planning. They have many workbooks for different projects and each project contains 1 or more sheets containing the actual staffing data:
The customer wants to consolidate all of the data from all of these many sheets and workbooks into a single pivot table. A 'consolidated' pivot is not an option because they want to be able to mess with all of the (non-date) fields in the source data. They don't want to be limited to only 'Row' and 'Column'. My current solution is a macro that consolidates all of the data within a workbook through a fairly convoluted copy and rotate process. I copy a row of 'meta data' (everything that's not a date) first, then I copy / transpose the dates for the meta data row into a single 'Date' column. Then I extend the meta data so that the same data is defined for each date.
I have a separate workbook that grabs the consolidated sheet from each workbook and builds a single pivot table from them.
It works, but it's pretty inefficient, since the total number of tasks / assignments number in the many thousands. In my dreams, I would love to eliminate the consolidation step completely, but I don't see that happening. A more efficient consolidation approach is about the best I'm hoping for at this point.
If anyone has some 'outside the box' ideas, I'm all ears! The solutions needs to work on windows XP, Office 2002 and 2003.