IF(ISERROR((GETPIVOTDATA) won't show any value once pivot table is updated with new information

[jessitarexcel]

I had a look through the forums but could not find a solution to this issue.

I have a series of worksheets with data being entered and changed on a daily basis. That data is collated into a single sheet as a summary. Some of the information is not currently available in the data and therefore the pivot tables. I have this formula in the cells that contain reference data: =GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","House"). If I have data for the reference area with the residence type house, it obviously returns the number. In the other cells, I would like to refer to the other residence types that don't appear as yet.

Example Table below:

Reference Area - House	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","House")	2
Reference Area - Unit	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","Unit")	#REF!
Reference Area - Boat	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","Boat")	#REF!
Reference Area - Caravan	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","Caravan")	#REF!

As there are only houses in the data so far, I am getting a #REF! area in the cells for the other residence types.

I then tried adding in ISERROR((=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","Caravan")) - which returns a true or false value and that allows the cells to be added up properly with the true being counted as a 0.

To show the result as a 0 instead of true, I added in =IF(ISERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","Boat"))),0,0), the result shows up as 0, but if I actually enter in a caravan as a test into the data, the pivot table shows the caravan, but the formula won't pick it up.

Is there anyway to keep the formula in the cell without it showing true or false but it will still update once the data has been entered? Any assistance is sincerely appreciated.

 

[Alex Blakenburg]

Any reason you are not using IfError ?

=IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","Caravan"),"")

 

[jessitarexcel]

Morning Alex, I have tried the IFERROR example you have provided above. The value now updates from the pivot table with the entry of new pivot table data, however, there is still no data showing:

Formula Used: =IFERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Redland City Council","Type of Residence","Boat")),"")

Reference Area - Boat
Reference Area - Caravan		This should show a 1 but it appears blank in my table using this formula =IFERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","Caravan")),"")
Reference Area - House	2	This formula is the standard one used =GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","House")
Reference Area - Unit
Reference Area - Total Applications	3	The total is adding in the one caravan that I added as a test, but it won't display.

I have turned off the Show a Zero in cells that have a zero value in the Advanced Options in Excel. I have checked other formatting issues. Do you know why the value is not showing in the table?

 

[jessitarexcel]

I am using Excel 365 in case that makes any difference

 

[Alex Blakenburg]

Are you using an Excel table and are you using that table name as the data source for your pivot ?
If not I would recommend adopting that approach. If not have you extended the Pivot's data source to include all the rows.
If you don't have any Caravan lines, can you put in a dummy one and after making sure of the above refresh the table.

If the formula then still doesn't return the value can you recreate the getpivot formula, it only takes a few seconds to do.
Can you post an XL2BB of where you have the getpivot formulas.
Ideally also an XL2BB of you Pivot (or part thereof)
Alternatively a desensitised version of your workbook on a sharing platform ie google drive, dropbox etc

MS 365 should be no different in terms of the GetPivotData functionality.

 

[jessitarexcel]

Hello Alex,

To answer your questions:
Are you using an Excel table and are you using that table name as the data source for your pivot? Yes, I always create tables and name them and then refer to the named Table in the Pivot Table. The Pivot Tables are located in the same document as the summary table but they are on separate tabs.

If not have you extended the Pivot's data source to include all the rows. My pivot table refers to the whole table and all of the data is contained within that table. There are no extra rows or columns included that aren't within the table range.

If you don't have any Caravan lines, can you put in a dummy one and after making sure of the above refresh the table. - I did enter a test which is how I discovered the issue. I put in one caravan and then refreshed the pivot table. I could see the caravan in the actual pivot table but I couldn't see it on the table that references it.

If the formula then still doesn't return the value can you recreate the getpivot formula, it only takes a few seconds to do. I will try recreating them this morning - that might solve the issue.

This is the Pivot Table below - stored on a separate tab:

BNE Tracker.xlsx AB AC AD 3 LGA Type of Residence Count of LGA 4 BCC Boat 10 5 Caravan 2 6 House 104 7 Unit/Townhouse 11 8 BCC Total 127 9 MBRC House 1 10 MBRC Total 1 11 RCC Caravan 1 12 House 2 13 RCC Total 3 14 (blank) (blank) 15 (blank) Total 16 Grand Total 131 SAG Pivot Tables - Locked

And this is the table that presents the data on a summary dashboard page - as you can see I have entered in a caravan test as the number has increased to 3, however, it won't show one in that cell for RCC Caravan.

BNE Tracker.xlsx B C 78 Key Figure Breakdown - LGA & Property Type 79 80 Summary Information Totals 81 BCC Boat 10 82 BCC Caravan 2 83 BCC House 104 84 BCC Unit/Townhouse 11 85 BCC Total Applications 127 86 RCC Boat   87 RCC Caravan   88 RCC House 2 89 RCC Unit/Townhouse   90 RCC Total Applications 3 91 MBRC Boat   92 MBRC Caravan   93 MBRC House   94 MBRC Unit/Townhouse   95 MBRC Total Applications 1 SAG Overview

 

[jessitarexcel]

It is quite a bizarre problem and that is why I am not sure how to resolve it.

 

[Alex Blakenburg]

I won't have access to a computer until later today. If you can remove sensitive data do you want to share it via Google drive. Drop box or some other sharing platform ?

 

[RoryA]

You seem to have used:

=IFERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","Reference Area","Type of Residence","Caravan")),"")

but Type of Residence is not a value field in that pivot table. I assume it should have been "LGA" as in the other formula.

 

[Alex Blakenburg]

Dittto to Rory's comment. Does this help at all ?
I have the totals in yellow since they are a different formula (which is not ideal).
The focus is on Column D the others are just to show how I got there.

20220912 Pivot Table GetPivotData jessitarexcel.xlsx
	B	C	D	E	F	G
78	Key Figure Breakdown - LGA & Property Type
79
80	Summary Information	Totals	Modified Formulas	Original Hardcoded (sample)	Test Prefix	Test part 2
81	BCC Boat	#REF!	10	10	BCC	Boat
82	BCC Caravan	#REF!	2	2
83	BCC House	#REF!	1	1
84	BCC Unit/Townhouse	#REF!	1	1
85	BCC Total Applications	#REF!	14	14
86	RCC Boat
87	RCC Caravan		1
88	RCC House	#REF!	1
89	RCC Unit/Townhouse	0
90	RCC Total Applications	#REF!	2
91	MBRC Boat	0
92	MBRC Caravan	0
93	MBRC House	0	1
94	MBRC Unit/Townhouse	0
95	MBRC Total Applications	#REF!	1
SAG Overview

Cell Formulas
Range	Formula
C81	C81	=GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC","Type of Residence","Boat")
D86:D89,D91:D94,D81:D84	D81	=IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$BD$3,"LGA",LEFT(B81,FIND(" ",B81)-1),"Type of Residence",RIGHT(B81,LEN(B81)-FIND(" ",B81))),"")
E81	E81	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC","Type of Residence","Boat")
F81	F81	=LEFT(B81,FIND(" ",B81)-1)
G81	G81	=RIGHT(B81,LEN(B81)-FIND(" ",B81))
C82	C82	=GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC","Type of Residence","Caravan")
E82	E82	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC","Type of Residence","Caravan")
C83	C83	=GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC","Type of Residence","House")
E83	E83	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC","Type of Residence","House")
C84	C84	=GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC","Type of Residence","Unit/Townhouse")
E84	E84	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC","Type of Residence","Unit/Townhouse")
C85	C85	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","BCC")
D85,D90,D95	D85	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$BD$3,"LGA",LEFT(B81,FIND(" ",B81)-1))
E85	E85	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$BD$3,"LGA","BCC")
C86	C86	=IFERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Boat")),"")
C87	C87	=IFERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Boat")),"")
C88	C88	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","House")
C89	C89	=IF(ISERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Unit/Townhouse"))),0,0)
C90	C90	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC")
C91	C91	=IF(ISERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Boat"))),0,0)
C92	C92	=IF(ISERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Caravan"))),0,0)
C93	C93	=IF(ISERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","House"))),0,0)
C94	C94	=IF(ISERROR((GETPIVOTDATA("Type of Residence",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Unit/Townhouse"))),0,0)
C95	C95	=GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","MBRC")