Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

Using Table Reference Syntax with a Multiple Consolidated Range Pivot Table

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.

Thanks,
Jerry
 Multiple-Consolidated-Ranges.xlsx Step1 Step2 Step 3a - Tablesstep3b.png
0
Jerry Paladino
Asked:
Jerry Paladino
  • 2
  • 2
1 Solution
 
dlmilleCommented:
I'm looking at this.  There are a couple solutions:

1.  Create an MS Query Union of both tables, then run your Pivot Table against that
2.  Insert a blank row in each table - not the perfect solution, but appears to work properly after that!
3.  Recreate the tables - this time, DON'T check "my table has headers" = see tip here:  http://tipsindeed.com/excel/data-consolidation-in-excel-made-easy-using-pivot-tables.html

This last one appears to be just as functional as #2 - creating the blank row.

I'm for #2, where you can maintain the column reference to Color and Type as opposed to Column 1 and Column 2 which could be a different column set on data refresh, if I'm reading your question correctly.

See attached.

Enjoy!

Dave
Multiple-Consolidated-Ranges.xlsx
0
 
Rory ArchibaldCommented:
Your syntax should be:

Tbl_Data1[[#All],[Color]:[Type]]

0
 
dlmilleCommented:
Nice!  Very.
0
 
Rory ArchibaldCommented:
Ta. Much.
:)
0
 
Jerry PaladinoAuthor Commented:
Works perfect - Thanks Rory!

Jerry
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now