title: How to build a consolidated pivot table when the source data contains column headings that are dates?
body: <p>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:</p>
  
  <p><img src="https://sites.google.com/site/daveparillo/downloads/stackoverflow/staffing.gif?attredirects=0" alt="Sample staff planning sheet"></p>
  
  <p>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.</p>
  
  <p>I have a separate workbook that grabs the consolidated sheet from each workbook and builds a single pivot table from them.</p>
  
  <p>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.</p>
  
  <p>If anyone has some 'outside the box' ideas, I'm all ears!
  The solutions needs to work on windows XP, Office 2002 and 2003.</p>