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?


--
aly1cat


------------------------------------------------------------------------
aly1cat's Profile: http://www.excelforum.com/member.php...fo&userid=4560
View this thread: http://www.excelforum.com/showthread...hreadid=567901

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

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


------------------------------------------------------------------------
aly1cat's Profile: http://www.excelforum.com/member.php...fo&userid=4560
View this thread: http://www.excelforum.com/showthread...hreadid=567901

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?


--
aly1cat


------------------------------------------------------------------------
aly1cat's Profile: http://www.excelforum.com/member.php...fo&userid=4560
View this thread: http://www.excelforum.com/showthread...hreadid=567901

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 he

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