Home

Excel GetPivotData Function

%3CLINGO-SUB%20id%3D%22lingo-sub-903411%22%20slang%3D%22en-US%22%3EExcel%20GetPivotData%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-903411%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20worksheet%20report%20that%20represents%20production%20activity%20in%20weekly%20periods%20(two%20columns%20per%20week).%20All%20columns%20are%20working%20correctly%20up%20until%20week%2040%20and%20after%20week%2040%2C%20where%20one%20specific%20cell%20is%20not%20populating.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20cell%20contains%20a%20series%20of%20formulas%20(added%20together%20to%20get%20total%20from%2010%20different%20pivot%20tables).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(GETPIVOTDATA(%22Sum%20of%20Reject%22%2C'%5BDaily%20Machine%20Tally%202019.xlsm%5DPivot%20Data'!%24AQ%243%2C%22WK%22%2CCG%243)%2C0)%2BIFERROR(GETPIVOTDATA(%22Sum%20of%20Reject%22%2C'%5BDaily%20Machine%20Tally%202019.xlsm%5DPivot%20Data'!%24AT%243%2C%22WK%22%2CCG%243)%2C0)%2BIFERROR(GETPIVOTDATA(%22Sum%20of%20Reject%22%2C'%5BDaily%20Machine%20Tally%202019.xlsm%5DPivot%20Data'!%24AW%243%2C%22WK%22%2CCG%243)%2C0)......%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%2010%20of%20the%20individual%20GetPivotData%20functions%20in%20week%2040%20column%20return%20a%20%23REF%20error.%20The%20data%20is%20displayed%20in%20the%20source%20pivot%20table%20(in%20another%20workbook).%20All%20other%20rows%2Fcolumns%20(weeks)%20are%20reporting%20correctly%20before%20and%20after%20week%2040%2C%20only%20a%20single%20cell%20in%20week%2040%20is%20not%20responding.%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20copy%2Fpaste%20the%20formula%20from%20other%20cells%20that%20are%20working%20to%20the%20week%2040%20offending%20cell%20with%20no%20results.%20If%20I%20reference%20another%20%22WK%22%20(i.e.%20CE%243%20instead%20of%20CG%243%20above)%20in%20the%20same%20cell%2C%20the%20data%20is%20displayed%20from%20previous%20week.%3C%2FP%3E%3CP%3ECell%20CG%243%20referenced%20in%20the%20formulas%20is%20%22%3DWEEKNUM(CG4)%22%2C%20where%20CG4%20is%20the%20date%20of%20the%20beginning%20of%20the%20week.%3C%2FP%3E%3CP%3ENot%20sure%20what%20is%20missing%20or%20incorrect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-903411%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-904295%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20GetPivotData%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-904295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423034%22%20target%3D%22_blank%22%3E%40Hwqi67%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThe%20GetPivotData%20creates%20an%20absolute%20reference%20and%20many%20Excel%20users%20prefer%20to%20turn%20of%20this%20functionality%20bygoing%20to%3A%3C%2FP%3E%3CUL%3E%3CLI%3EFile%20Tab%3C%2FLI%3E%3CLI%3ESelect%20Options%3C%2FLI%3E%3CLI%3ESelect%20Formulas%3C%2FLI%3E%3CLI%3EUncheck%20the%20box%20for%20GetPivotData%3C%2FLI%3E%3CLI%3EHit%20OK%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136555iBD46C3457673469D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Get%20Pivot.png%22%20title%3D%22Get%20Pivot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20That%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-912627%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20GetPivotData%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423034%22%20target%3D%22_blank%22%3E%40Hwqi67%3C%2FA%3E%26nbsp%3B%20Figured%20out%20that%20when%20the%20month%20changes%20in%20the%20middle%20of%20the%20week%2C%20that%20week%20is%20in%20the%20previous%20month%20as%20well%20as%20the%20next%20month%20-%20two%20data%20references%20to%20the%20same%20week%20confuses%20GetPivotData.%20I%20separated%20the%20month%20data%20from%20the%20weekly%20data%20to%20solve%20the%20problem.%3C%2FP%3E%3CP%3EThanks%20for%20any%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Hwqi67
New Contributor

I have a worksheet report that represents production activity in weekly periods (two columns per week). All columns are working correctly up until week 40 and after week 40, where one specific cell is not populating.

 

That cell contains a series of formulas (added together to get total from 10 different pivot tables). 

 

=IFERROR(GETPIVOTDATA("Sum of Reject",'[Daily Machine Tally 2019.xlsm]Pivot Data'!$AQ$3,"WK",CG$3),0)+IFERROR(GETPIVOTDATA("Sum of Reject",'[Daily Machine Tally 2019.xlsm]Pivot Data'!$AT$3,"WK",CG$3),0)+IFERROR(GETPIVOTDATA("Sum of Reject",'[Daily Machine Tally 2019.xlsm]Pivot Data'!$AW$3,"WK",CG$3),0)......

 

All 10 of the individual GetPivotData functions in week 40 column return a #REF error. The data is displayed in the source pivot table (in another workbook). All other rows/columns (weeks) are reporting correctly before and after week 40, only a single cell in week 40 is not responding.

I have tried to copy/paste the formula from other cells that are working to the week 40 offending cell with no results. If I reference another "WK" (i.e. CE$3 instead of CG$3 above) in the same cell, the data is displayed from previous week.

Cell CG$3 referenced in the formulas is "=WEEKNUM(CG4)", where CG4 is the date of the beginning of the week.

Not sure what is missing or incorrect.

 

2 Replies

@Hwqi67 

Hi

The GetPivotData creates an absolute reference and many Excel users prefer to turn of this functionality bygoing to:

  • File Tab
  • Select Options
  • Select Formulas
  • Uncheck the box for GetPivotData
  • Hit OK

 

Get Pivot.png

 

Hope That Helps

Nabil Mourad

@Hwqi67  Figured out that when the month changes in the middle of the week, that week is in the previous month as well as the next month - two data references to the same week confuses GetPivotData. I separated the month data from the weekly data to solve the problem.

Thanks for any assistance.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies