Hi,

I know there is a plethora of pivot table and index/match posts, and I
feel I've gotten close to resolving my issue, but no dice so far. I've
got a workbook that is connected to an external dataset. The workbook
is just a pivot table that pulls data from the external dataset. It
shows sales counts for all countries worldwide for all or selected
years. The pivot table is looking at a specific product, with
countries vertically in column B, and the years horizontally in row 4,
and of course with the data counts in the adjacent cells.

I have a second worksheet that has a specific subset of countries for
which I need to return the data from the main pivot table.

I first tried a vba solution with a slicer, programming all the
countries in the subset, but the macro bugged out if it encountered a
country in the subset that wasn't in the main pivot table for the
given device.

I do have a 'GETPIVOTDATA' formula working, and the only problem with
that is it doesn't account for the dynamically expanding/contracting
years that appear in the pivot table.

I also looked at an Index/Match function, but I need the INDEX part to
be a lookup of the year on the second sheet to the year in the main
pivot table.

In a nutshell, it seems I'm trying to overcomplicate this. Any
thoughts on how best/easiest to make a second pivot table that looks
at a subset of countries?

Thanks

Perhaps this will be helpful...

http://www.contextures.com/pivottableindex.html

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Thanks for the response. There's a LOT of great info about pivot
tables at that site! I looked through it carefully, and unfortunately
it doesn't quite get me where I need to be.

People can and do go into the pivot table and manually filter by the
subset of countries, but there are 33 countries out of the much larger
list that need to be filtered every time, so it's tedious and prone to
possibly missing a country. My goal was to hopefully find a way to
either automatically apply the country subset filter, or on another
sheet, do an index/match type formula to pull the data for those
countries over. Either way would be acceptable.

I can move the VBA question over to the other group, but just to show
here what I tried, below is a small part of the code for applying that
subset of countries filter. It works, so long as the country is
actually in the data the pivot table returns for the given product
that is being looked at. If the product isn't sold in that country,
say Belgium for instance, the macro bugs out...because it can't filter
on something that isn't there.

Sub SetEUcountries()
.VisibleSlicerItemsList = Array( _
"[Financial Org].[Top Countries].[Country Name].&[Other
OUS]&[AUSTRIA]", _
"[Financial Org].[Top Countries].[Country Name].&[Other
OUS]&[Belgium]", _
"[Financial Org].[Top Countries].[Country Name].&[OUS]&[UNITED
KINGDOM]")
End Sub


As for Index/Match, that also works when I initially set up the
formula, because the array I want to index, i.e., the year of the data
is set from the data returned in the pivot table. For example, 2017
data is in column F. But if a different product is looked at in the
pivot table (the main filter), then the 2017 data may move to column
G, i.e., the pivot table returned an additional year of data. So the
array for the Index part of the formula is dynamic, as is the array in
the Match portion of the formula. I've tried expanding the Index
array and using a dynamic range for the Match, as shown below, but
that returns #N/A. Any thoughts on this?

=INDEX('Sales Figs'!C:Z,MATCH('Countries'!K2,'Sales
Figs'!C4:INDEX('Sales Figs'!4:4,1,COUNT('Sales Figs'!4:4)),0))

Thanks Garry.


On Fri, 28 Apr 2017 01:04:06 -0400, GS wrote:

Perhaps this will be helpful...

http://www.contextures.com/pivottableindex.html

To be perfectly honest.., I never use pivot tables! I tend to handle data in
the way a database app would, where I use a userform with a data grid and
controls that allow users to do criteria based queries on the underlying
'recordset'.

Pivot tables only work in spreadsheet apps that support them. Database queries
work everywhere! This allows me to reuse the same code in VB6 apps as well. I
see pivot tables as a user friendly alternative to viewing data by changing
criteria in the table. Nice for spreadsheet users not versed in db programming!
(just saying...)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I can't and won't argue with that logic. I don't have much of a
choice, though, since it's both not my database and not my pivot
table. I'm simply trying to find an easy solution to take a time
consuming and error prone step or two out of the process.

So that said, any thoughts on how I might go about at least doing an
index/match where the indexed array is dynamic as well as the matched
array (which is in a row vice a column)?

Thanks!!

Frank

On Fri, 28 Apr 2017 14:50:50 -0400, GS wrote:

To be perfectly honest.., I never use pivot tables! I tend to handle data in
the way a database app would, where I use a userform with a data grid and
controls that allow users to do criteria based queries on the underlying
'recordset'.

Pivot tables only work in spreadsheet apps that support them. Database queries
work everywhere! This allows me to reuse the same code in VB6 apps as well. I
see pivot tables as a user friendly alternative to viewing data by changing
criteria in the table. Nice for spreadsheet users not versed in db programming!
(just saying...)

So that said, any thoughts on how I might go about at least doing an
index/match where the indexed array is dynamic as well as the matched
array (which is in a row vice a column)?

Sorry.., I just don't have enough experience with pivot tables to be much help
to you. Hopefully someone else may chime in...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Friday, 28 April 2017 11:37:13 UTC+10, Phrank wrote:
Hi,

I know there is a plethora of pivot table and index/match posts, and I
feel I've gotten close to resolving my issue, but no dice so far. I've
got a workbook that is connected to an external dataset. The workbook
is just a pivot table that pulls data from the external dataset. It
shows sales counts for all countries worldwide for all or selected
years. The pivot table is looking at a specific product, with
countries vertically in column B, and the years horizontally in row 4,
and of course with the data counts in the adjacent cells.

I have a second worksheet that has a specific subset of countries for
which I need to return the data from the main pivot table.

I first tried a vba solution with a slicer, programming all the
countries in the subset, but the macro bugged out if it encountered a
country in the subset that wasn't in the main pivot table for the
given device.

I do have a 'GETPIVOTDATA' formula working, and the only problem with
that is it doesn't account for the dynamically expanding/contracting
years that appear in the pivot table.

I also looked at an Index/Match function, but I need the INDEX part to
be a lookup of the year on the second sheet to the year in the main
pivot table.

In a nutshell, it seems I'm trying to overcomplicate this. Any
thoughts on how best/easiest to make a second pivot table that looks
at a subset of countries?

Thanks

Hi Phrank

My suggestion would be to make a list of the countries you want to select, in another sheet, and use that as the lookup source.
Next make a sheet that is a copy of your source data by reference, ie make a data sheet where each cell is a ref to your source sheet.
In your data sheet add in the match to your country list then use this data to feed a new pivot. Use an if expression to set it to include/exclude based on the result of the match being a number, isnumber(match()),"Include","Exclude") as your country filter
Your country filter field will be available for a slicer....
ta da :)

Ron