Pivot Table Data - Troubleshoot needed

[aly1cat]

I am trying to use the getpivotdata formula and am stubbling on som
problems with the formula I am using.
=GETPIVOTDATA("Count",Sheet4!$A$6,"Objective ","001","Objective
Title","External Service Management","Sub OC","CMP")
I am basically trying to get data (for a summary sheet) from a pivo
table, however the pivot table does not display a heading which has n
data. So if my formula was asking for the pivot table to gather dat
from a column titled Ext Service Management it comes back as a "REF" a
the pivot table is not showing any for that particular title as ther
are none.

Question is how can I get either:
1. the pivot table to show the title even if no data is there
2. how I can change the formula to return a nil value is there is n
title and no data

 

[Debra Dalgleish]

1. You can double-click on a field button, and add a check mark to 'Show
items with no data'

2. You can use an IF function with the GetPivotData function. For example:

=IF(ISERROR(GETPIVOTDATA("Units",$A$6,"Item",F9)),0,
GETPIVOTDATA("Units",$A$6,"Item",F9))

 

[aly1cat]

thanks, but still not sure how to complete the formula i.e yours i
considerably different from mine and what does the F9 do

 

[aly1cat]

Also, I do not want to test the error, I just want it to look for the
item and if it is not in the pivot table, then just report it back as a
"0".

Is this possible?

 

[Debra Dalgleish]

If the pivot table layout won't change, you could use the Match function
to look for an item in a range. There are examples here:

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

If the function returns a number, the item was found.

But the GetPivotData function may be easiest to use, to test if the item
is in the pivot table. Using your example:

=IF(ISERROR(GETPIVOTDATA("Count",$A$6,"Objective","001",
"Objective Title","External Service Management","Sub OC","CMP"))
0, GETPIVOTDATA("Count",$A$6,"Objective ","001",
"Objective Title","External Service Management","Sub OC","CMP"))