Is there a way to use VSTACK to get a PivotTable range?

TomCon

Active Member
Joined
Mar 31, 2011
Messages
386
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I want to create a PivotTable from a non-contiguous range. Of course, this can be solved by making an additional sheet with the concatenation of the two ranges. But, i am wondering if it is possible to accomplish this without making an additional copy of the raw data. These ranges are large, variable in number (sometimes 2 discontinuous ranges, sometimes 3, etc), so i would like to avoid the "make a copy solution".

For example, lets take this VSTACK command:
=VSTACK(A1:C5,H1:J2)
I can use it in functions like COUNT:
=COUNT(VSTACK(A1:C5,H1:J2))

I have defined a range named PivTst as follows:
=VSTACK(Sheet1!$A$1:$C$5,Sheet1!$H$1:$J$2)
If i just type the above in a cell, it does give a range that is correct for a PivotTable, i.e. it has column headers at the top.

I can now use COUNT as follows:
=COUNT(PivTst)

I was hoping to be able to use one of the above as the definition of a PivotTable range.
I tried both of the following in the PivotTable TableRange field:
VSTACK(A1:C5,H1:J2)
PivTst

But, both fail.

Any ideas as to how i could accomplish this with maximum flexibility and avoiding making several copies of the raw data in various combinations?

Thank you.
 

Forum statistics

Threads
1,226,772
Messages
6,192,928
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top