Using the Pivot Table Wizard in Excel 2007 (Alt+D+P)
I would like to use Table reference syntax like "Tbl_Data1[Color]" as the range reference in a Multiple Consolidate Range Pivot Table. When the columns of data are referenced with column references "Data1!$A:$B" all the data is returned to the Pivot. When the columns of data are referenced with Table reference "Tbl_Data1[[Color]:[Type]]
" the first row of each data set is not returned. I believe it assumes the first data value is the column header (which it is not w/ table reference).
Is there a way use the Table reference and return all the data? I am concerned about using column reference because the data sets can have columns add or deleted, making the reference invalid in the Pivot.