How to consolidate multiple worksheets to a PivotTable in Excel 2007
I'm trying to consolidate two worksheets to a pivot table, all in the same workbook. I use the pivot table wizard and select consolidate from multiple sources. I enter the active cells from worksheet1 and then worksheet2 and select finish. The worksheets have some common columns (same name) which I don't want duplicates of in the Pivot.
I'm not able to drag from the field list to the report (col,row,val) like you would for a pivottable based on a single worksheet. Am I missing a step? It seems that this should be a standard feature because the wizard supports data from multiple sources.