Pivot Table Data - Troubleshoot needed

[aly1cat]

I am trying to use the getpivotdata formula and am stubbling on some 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 pivot table, however the pivot table does not display a heading which has no data. So if my formula was asking for the pivot table to gather data from a column titled Ext Service Management it comes back as a "REF" as the pivot table is not showing any for that particular title as there 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 no 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 wrote:
> I am trying to use the getpivotdata formula and am stubbling on some
> 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 pivot
> table, however the pivot table does not display a heading which has no
> data. So if my formula was asking for the pivot table to gather data
> from a column titled Ext Service Management it comes back as a "REF" as
> the pivot table is not showing any for that particular title as there
> 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 no
> title and no data?
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

[aly1cat]

thanks, but still not sure how to complete the formula i.e yours is 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"))



aly1cat wrote:
> 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
Contextures
http://www.contextures.com/tiptech.html