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.
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.