GETPIVOTDATA – How to force getpivotdata to accept missing data

I have a project where users must select options from data validation cells. Based on those selections I have formulas that will decide which pivot table to get the data from, and which data to get. The issue I am having is the formulas are getting long because not all of the tables have the same number of rows and column which means that the GETPIVOTDATA formulas don’t have the same number of items.
I would like to do something like this…

=GETPIVOTDATA(“CaseNb”,Sheet1!$A$1,if(Z1=1,”item1, itemA”,””),if(z2=1,”item2,itemB”,””), etc)

So I want to use one GETPIVOTDATA formula whether the data exists or not. However, I haven’t been able to find the correct syntax. Part of the problem is that I would have to use double double quotes like this if(Z1=1,””item1”,”itemA””,””) because the GETPIVOTDATA function requires the items to be surrounded by double quotes, like this =GETPIVOTDATA(“CaseNb”,Sheet1!$A$1,”item1”,” itemA”, …)
Anyone have any idea how to make this work?

Thanks

Assume it’s a sales analysis
Users can select
AA1 - Zone (North, South, East, West, All)
AA2 - Product Line(Soccer Equip, Baseball Equip, Football Equip, … , All)
AA3 - Product Type (Balls, Protective Gear, Shoes, … , All)
I have several pivot tables that provide this information in several different formats. Some by month, some by YEAR, some by product type, some by product line, etc. I have to use several pt because of the different rollups needed.

I have some logic that decides which table has the data I need and the table location is in cell Q1. So Q1 will have a value of “Tables$A$1”, or “Tables!$F$1”, etc
So If the user selects All Zones, All Product Lines, All Prod Types the formula would look like this
'=GETPIVOTDATA("ItemsSold",INDIRECT($Q$1))

If the user selects All Zones, Soccer Equip, Balls the formula would look like this
'=GETPIVOTDATA("ItemsSold",INDIRECT($Q$1), “Product Line”,” Soccer Equip”,”ProdType”,”Balls”)

Currently I am using a formula that says
If(scenario = 1, GETPIVOTDATA("ItemsSold",INDIRECT($Q$1)),
if(scenario = 2, GETPIVOTDATA("ItemsSold",INDIRECT($Q$1), “Product Line”,AA2,”ProdType”,AA3),etc

And so the formula grows to accommodate all of the combinations.

I would like to just say
GETPIVOTDATA("ItemsSold",INDIRECT($Q$1),if(AA2<>”All”,””ProductLine”,AA2”,ignore), if(AA3<>”All”,””ProdType”,AA3”,ignore), …)

I know ignore is not an option I am just trying to show that I don’t want Excel to do anything but also don’t cause an error.
I hope that makes sense, and Thanks again for your help RomperStomper

Actually that is my next path if this turns into a dead end. any ideas ?

I intend to use vba to change the pagefields of the pivot tables based on the user selections. That is how I will make the pivot tables display the correct information. I don't, however, know how that will help reduce the size of the formulas that I was originally asking about. If there is anyway to force GETPIVOTDATA to ignore empty items it would make things much easier. Or is there some other way to do this that I havent thought of yet?

thanks

I wish I could, but it's HR data so I can't post it without recreating it with false data. Here's the problem. I tried it with page fields in the pivots and macro's to change them driven by the user selections. However, I am finding that by doing it that way it take 90 seconds or more to cycle thru the macro (the users like the product but are complaining about how long it takes to run). I have even added the PivotTable.ManualUpdate.True/False lines to try to speed it up but, no joy. Historically, when I just put the data in the pivots with all the row/column fields I need, and have the worksheet formulas get the data based on the selections without manipulating the pivot tables it runs much faster. I am up against the wall, and I am afraid I am going to have to create huge formulas to get the data with many nested IF's unless I can come up with a solution to how to force GETPIVOTDATA to accept missing data.

Thanks

I think I’m almost there, but I am still getting errors…maybe it’s just not possible???
In cell S16:T18 I have this
1 ,"Region","West"
1 ,"ProductLine","Soccer Equip"
1 ,"ProductType","Balls"

Then I have the formula
=INDIRECT(CONCATENATE("=GETPIVOTDATA(""CaseNb"",Tables!$JA$9,""OpenDt"",12,""Years"",2014") & IF($S$16<>0,$T$16,"") & IF($S$17<>0,$T$17,"") & IF($S$18<>0,$T$18,"") & ")")
Without the “=INDIRECT” part it produces the correct formula …
=GETPIVOTDATA("CaseNb",Tables!$JA$9,"OpenDt",12,"Years",2014,"Region","West","ProductLine","Soccer Equip","ProductType","Balls")

If S18 = 0
1 ,"Region","West"
1 ,"ProductLine","Soccer Equip"
0 ,"ProductType","Balls"
then the result is

=GETPIVOTDATA("CaseNb",Tables!$JA$9,"OpenDt",12,"Years",2014,"Region","West","ProductLine","Soccer Equip")

Looks good so far … So I added the=INDIRECT and got a REF# error.
So I got rid of the Concatenate and tried
=INDIRECT("GETPIVOTDATA(""CaseNb"",Tables!$JA$9,""OpenDt"",12,""Years"",2014"&IF($S$16<>0,$T$16,"")&IF($S$17<>0,$T$17,"")&IF($S$18<>0,$T$18,"")&")")
Which also produces a correct “looking” formula. But again when I add =INDIRECT I get the REF# error.
Any ideas?

RomperStomper, Thanks a lot for your help. This looks and sounds like what I am looking for. At this point though I have to plead ignorance. I do not understand one letter of your function, sorry about my rudimentary vba skills. I added the function to my project and tried this …

=datafrompivot("Sales",Tables!$JA$9,"Region","West","ProductLine","All","ProductType","All")

But I am still getting the REF# error. Can you see anything I am doing wrong ?

it returns a number.

I tried starting with a valid GETPIVOTDATA formula that works, I then changed GETPIVOTDATA to DATAFROMPIVOT and got a #VALUE error ??

I wish I could. But if I did I would have a whole new set of problems. So I tried to recreate the issue to show you with some false data I typed into a spreadsheet and built a pivot table and tried the datafrompivot and it worked ! It appears that the reason it is giving me an error is that in my real project I have dates. when I add dates into the pivot it fails. Apparently the Function doesnt handle dates ? Additionally, my pivots have the dates grouped by Month and Year (but it is failing with or without the grouping).

works like a charm until I add a date... then it fails. Also I created some pivot tables to show you but I cant figure out how to attach it. I will have to spend some time in the FAQ.

That one works! So let me just say that I thought I knew a thing or two about Excel. RomperStomper you are the best. I can't tell you how much this will improve my life. Thanks for taking the time to understand my problem and offer solutions and for not giving up when it started to get complicated. I wish I could buy you a drink. Thanks again!

Romperstomper, Any idea why this function takes so long to calculate? I have checked to make sure automatic calculation is checked. When I make a change to the dropdown it takes a long time to recalculate the cells that use this function. If I do an manual recalculate (ctrl + =) it completes the calculations on those cells instantly. I added a line to my event procedure "Sheets("sheet1").Calculate" which seems to help but I dont know what I will do if I dont have an even procedure to manually recalculate. any idea whats causing this?

Thanks

The problem appears to be that DataFromPivot is not in the calculation tree??? when I change the dropdown (data validation) it runs everything else but the cells with DataFromPivot. As soon as I do anything else (even F2/Enter on a random cell) it recalculates the wb including the DataFromPivot cells and everything is updated. But Not until I force it.

Here is the event procedures from the data validation dropdowns. "Sheets("Compile").Calculate" is the lines I added to deal with it.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$3" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Call ClearGroupChange
Sheets("Compile").Calculate
Range("D4").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

If Target.Address = "$D$4" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Call ClearSubGroupChange
Sheets("Compile").Calculate
Range("D5").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

If Target.Address = "$D$5" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Call ClearCaseTypeChange
Sheets("Compile").Calculate
Range("D6").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

End Sub

Private Sub ClearGroupChange()
Range("D4").Select
ActiveCell.FormulaR1C1 = "All"
Range("D5").Select
ActiveCell.FormulaR1C1 = "All"
Range("D6").Select
ActiveCell.FormulaR1C1 = "All"
End Sub

Private Sub ClearSubGroupChange()
Range("D5").Select
ActiveCell.FormulaR1C1 = "All"
Range("D6").Select
ActiveCell.FormulaR1C1 = "All"
End Sub

Private Sub ClearCaseTypeChange()
Range("D6").Select
ActiveCell.FormulaR1C1 = "All"
End Sub

I added the "Application.Volitile True" line (actually I replaced the entire function) and without the "Sheets("Compile").Calculate" line in the event it still wont calculate.

At first I thought it may be because I added the Function to both a module in the project and a module in my personal Macro wb. I removed the one in my personal macro wb but still no auto calc.

maybe it has to do with my formula. When I just do a getpivotdata on a pt and then change it to datafrompivot and change some of the items to all it works fine. my formulas look like this...
=IFERROR(datafrompivot("Sales",INDIRECT($A$10),"OpenDt",MONTH(F$2),"Years",YEAR(F$2),"Region",$B10,"ProductLine",$C10,"ProductType",$D10),0)
Where A10 holds the location of the pt
F2 holds the date
B10 changes based on the region selected by the user
etc
Maybe because of all the cell changes it's just calculating out of order? meaning a change is made and datafrompivot is calculated and then the cells change and it isnt recalculated. I am thinking that because all of the cells with the datafrompivot do change to zeros.

No. The INDIRECT($A$10) is there because if the user wants to see total Balls sold in 2014 that will be in a different table than if they want to see soccerballs sold in 2014. so the data is cut differently in different tables to cover all of the combinations of requests the user may make. Because I dont want to have a different formula each time a different table is required I created a cell (A10) with some logic that determines which table has the data requested. So cell A10 contains the text "Tables!$JA$9" for example. I just point the formula there and I only need one, but it needs an INDIRECT to make that work..
I hope that makes sense...

removed INDIRECT from this working formula =datafrompivot("Sales",INDIRECT($A$10),"OpenDt",MONTH(F$2),"Years",YEAR(F$2),"Zone",$B10,"ProductLine",$C10,"ProductType",$D10) result was a #REF! error.

when I just removed the word INDIRECT and the associated parentheses like this
=datafrompivot("Sales",$A$10,"OpenDt",MONTH(F$2),"Years",YEAR(F$2),"Zone",$B10,"ProdLine",$C10,"ProdType",$D10)
I get a #REF! error

When I put the actual valid table reference...like this
=datafrompivot("Sales",Tables!$JA$9,"OpenDt",MONTH(F$2),"Years",YEAR(F$2),"Zone",$B10,"ProdLine",$C10,"ProdType",$D10)
I get a #VALUE! error

I am not sure if that helps answer the calculation issue or not. Normally when I run it all the cells with the DataFromPivot have a value of zero until I force a calculation. The cells I was testing changed to the errors.

No. sorry. What I am saying is they both work AFTER I do a manual calculation on the wb. If I just change a value in the dropdown that kicks off the event procedure this one...
=datafrompivot("Sales",INDIRECT($A$10),"OpenDt",MONTH(F$2),"Years",YEAR(F$2),"Zone",$B10,"ProdLine",$C10,"ProdType",$D10)
results in zero until I do a manual wb calc.
And this one
=datafrompivot("Sales",Tables!$JA$9,"OpenDt",MONTH(F$2),"Years",YEAR(F$2),"Zone",$B10,"ProdLine",$C10,"ProdType",$D10)
results in a #VALUE! error UNTIL I do a manual wb calc.

AFTER I do a manual wb calc both result in the correct value.

I think what may be happening is that the DataFromPivot formulas are being calculated immediately once the event kicks off. The event changes the fields that fill the missing values of the DataFromPivot function. Since those fields are mixed or even invalid until the event processing is complete the DataFromPivot returns an ERROR (or zero since I am using IFERROR(DataFromPivot(...),0)) and then once the fields change to the correct values DataFromPivot is not recalculated. So it may be calculated out of order... I resolve this by adding the line "Sheets("Compile").Calculate" to force it to recalculate at the end of the Event Procedure.

All along my concern has been, what happens if I use this function DataFromPivot without the Event procedure (no Event procedure then no place to add the line). After some testing I have found that without the Event Procedure the Function works flawlessly. so I believe that the combination of the Event and the Function is the problem (Calc order ??) which I have solved by forcing a wb calculation as the last step of the Event. not sure if there is some way to do that in the function or not but my original problem is solved and my concerns with using the Function without an Event is also solved. If there is no way to move the function to the end of the calculation tree it still works perfectly with the forced calc in the Event.

So RomperStomper, unless there is a way to force DataFromPivot to be calculated last in the wb I think we are there. Just need to force a recalc in the Event OR use it without an Event.

Excellent. And Thanks Rory. Obviously, I am Scott.

Rory, I have been using your datafrompivot Function with great success. I have applied this function to several projects and it works GREAT.
I have however, run into a problem. When you provided this function I told you I didn’t really understand it. I have been spending a lot of time studying it and reading about VBA for Applications to better understand. But what has helped me the most is stepping thru it using the Locals Window. I tell you this because I don’t want you to think I am going to run to you every time I have a problem. I am trying to learn more about VBA.
Now the problem… I recently applied this function to a project that previously used GETPIVOTDATA and worked fine. When I converted to datafrompivot (due to added complexity from a rebuild) I got errors. Using the debugger I think I have figured out the problem, but I don’t know how to fix it (yet).
The column field on the pivot table is “JobCode”. The data in this field is Numbers stored as text. When I run the function thru the debugger the “JobCode” is handled by the line

Please Login or Register  to view this content.

The result is “JobCode”, 402

Where I think it should be handled by the line

Please Login or Register  to view this content.

so the result would be JobCode”, “402”

Any idea what I can add to this function to handle Numbers stored as text? Is there another keyword other than IsNumeric that I could use to handle Numbers stored as Numbers?

Thanks