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.

 

[jessitarexcel]

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.

Hello Rory, Type of Residence is a value field. The Pivot Table is displaying the LGA and Type of Residence. I am a little confused as to what you mean. All of the other formulas that return values show Type of Residence in the formula. The reason there are variances in the formula is that some of the information is not currently available. If I put in the normal GetPivotData formula that refers to data that is not yet available in the Pivot Table, then I get a #REF! value and the table won't total properly. I can make all REF's a 0 and then make the 0 disappear by changing an option in the settings, but I want the formulas there so that once the data is entered, it appears properly and I don't have to go back to change each formula manually at a later date.

 

[Alex Blakenburg]

In your xl2bb of your pivot table it has the "type of residence" in the position of a Row Label / Row Field. A value field sits in the Data Area of the pivot and will have something like Sum, Count, Max, Min etc applied to it.
Did you try copying my xl2bb into cell b78 of a blank sheet in your workbook?

 

[jessitarexcel]

In your xl2bb of your pivot table it has the "type of residence" in the position of a Row Label / Row Field. A value field sits in the Data Area of the pivot and will have something like Sum, Count, Max, Min etc applied to it.
Did you try copying my xl2bb into cell b78 of a blank sheet in your workbook?

Thank you for your help and thank you also to Rory. I think I have resolved the issue and I understand what yourself and Rory were referring to. I will copy over the solution shortly. I haven't tried your suggestion as yet but I also think that is a good solution. I have also fixed up all of the formulas to be uniform. I feel so silly that it was such a simple fix and I just couldn't see it. It is such a monster of a spreadsheet with so many different formulas and I think my eyes were going square trying to figure it out. Sometimes the simplest solution works. Give me just a few minutes and I will put up my new table.

 

[jessitarexcel]

Post Approval Tracker - BNE Data.xlsx B C 80 Summary Information Totals 81 BCC Boat 10 82 BCC Caravan 2 83 BCC House 105 84 BCC Unit/Townhouse 11 85 BCC Total Applications 128 86 RCC Boat 0 87 RCC Caravan 1 88 RCC House 2 89 RCC Unit/Townhouse 0 90 RCC Total Applications 3 91 MBRC Boat 0 92 MBRC Caravan 0 93 MBRC House 1 94 MBRC Unit/Townhouse 0 95 MBRC Total Applications 1 SAG Overview Cell Formulas Range Formula C81 C81 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","BCC","Type of Residence","Boat"),"0") C82 C82 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","BCC","Type of Residence","Caravan"),"0") C83 C83 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","BCC","Type of Residence","House"),"0") C84 C84 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","BCC","Type of Residence","Unit/Townhouse"),"0") C85 C85 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","BCC"),"0") C86 C86 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Boat"),"0") C87 C87 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Caravan"),"0") C88 C88 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","House"),"0") C89 C89 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC","Type of Residence","Unit/Townhouse"),"0") C90 C90 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","RCC"),"0") C91 C91 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","MBRC","Type of Residence","Boat"),"0") C92 C92 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","MBRC","Type of Residence","Caravan"),"0") C93 C93 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","MBRC","Type of Residence","House"),"0") C94 C94 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","MBRC","Type of Residence","Unit/Townhouse"),"0") C95 C95 =IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables - Locked'!$AB$3,"LGA","MBRC"),"0")

 

[jessitarexcel]

I did a test and removed the caravan and the formulas worked exactly as they should. Thank you so much everyone! I sincerely appreciate all of your help.

 

[Alex Blakenburg]

You're welcome. Glad we could help.

If you want to, try this in C81, it should work for all the data lines.

=IFERROR(GETPIVOTDATA("LGA",'SAG Pivot Tables -  Locked'!$AB$3,"LGA",LEFT(B81,FIND(" ",B81)-1),"Type of  Residence",RIGHT(B81,LEN(B81)-FIND(" ",B81))),"")