GETPIVOTDATA and Cell Referenec issue driving me crazy!

**derrickallen** · 02-06-2012, 12:49 PM

Hey forum,
I'm trying to perform some GETPIVOTDATA functions and I've searched on this forum and several others and the criteria that I'm trying to use is not working correctly but it SHOULD be and I can't figure out why.

I have a pivot sheet in my workbook with all of my data. I have another sheet that I'm wanting to put some GETPIVOTDATA cells to pull in information. This is the basic setup I have for those cellls, with the cellreference that isn't working. It's looking for a badge number cell on the sheet, then pulling the FW data from the appropriate place in the pivot based on the second cell reference. The SECOND cell reference is working fine, but whenever the Badge is referencing a cell I just continue to get #REF!. Now if I replace the Badge reference cell with the ACTUAL number of the badge then then everything works fine. I've no idea why the second cell reference works but the first one does not:

Not working:
=GETPIVOTDATA("AgentAHT",AHTPivot!$A$5,"Badge",$E$10,"FW",$H$17)

Working:
=GETPIVOTDATA("AgentAHT",AHTPivot!$A$5,"Badge",123456,"FW",$H$17)

It might be important to mention as well that the Badge reference cell, $E$10, is pulled up based on a dropdown that matches a name with HR data to pull the badge. However, I have also just tried to reference the cell WITHOUT any code and just having a plain number and that delivers the exact same results in the getpivotdata cell.

Badge Pull-Up Cell, however just typing 123456 in the cell and then referencing does NOT work as well:

=INDIRECT(IFERROR(CONCATENATE("ROSTER!A",MATCH(C10,ROSTER!$B:$B,0)),""),TRUE)

Any assistance would be great. Right now I'm pulling all the data myself in multiple pivot sheets but I'd like ONE master sheet where I can just choose an employee from a dropdown and setup a way to see all their year to date data in a nice and neat sheet.

**OnErrorGoto0** · 02-06-2012, 01:02 PM

Perhaps
=GETPIVOTDATA("AgentAHT",AHTPivot!$A$5,"Badge",--$E$10,"FW",$H$17)

**derrickallen** · 02-06-2012, 01:24 PM

Seems to be working. Why do you think that would be? These types of little nuances is what I mainly bang my head against the desk for. Why can't they just work!!

**OnErrorGoto0** · 02-06-2012, 01:48 PM

I assume what you have in E3 is a text representation of a number, whereas the actual data is a true number value.

**derrickallen** · 02-06-2012, 06:48 PM

Here's another issue I'm experiencing on the same sheet.

I got the cell references to all work. However, some of the data that I'm pulling from the pivot sheet is broken down into Fiscal Years, Fiscal Quarters, Fiscal Weeks. I do some montly reporting as well but if I re-arrange the Pivot table to include monthly, I get a problem. What will hapen is that some months have a few trailing or ending days that may be in a different quarter. So when you're looking at their breakdown section of the Pivot, you may see a month in Q3 but then you'll also see the month in Q4. This breaks GETPIVOTDATA from being able to pull that month UNLESS you include which Quarter you want to pull it from as well in your GETPIVOTDATA string.

If I remove the Quarter as an option in the pivot table then I can pull the month because the pivot table combines the data since it's not being broken down by quarters anymore but I need my report page to show various data in Weeks, Months, AND Quarters. Is there a way to tell the string to pull the data from the sections of the month in BOTH quarters and combine them to one number? Am I missing something here?

**OnErrorGoto0** · 02-06-2012, 06:54 PM

GETPIVOTDATA will only return a result from one cell so, unless you can produce a subtotal that combines the two somehow, you will need to add two subtotal formulas together.

**KeithP** · 12-19-2016, 03:46 PM

I have had similar issues with referencing cells in Pivot Tables and copying formulas down a column. Even with GetPivotData unchecked, the calculated cell remains the same as the first cell. However by chance, I made the Calculations option (File, Options, Formulas, Calculation Options) 'Automatic' and cell references could then be copied down the column. Hope this may be of assistance to Pivot Table users.