The 'GetPivotData' formula does not work
When I work on the excel files provided on lesson "GetPivotData is great! Extracting data from the Master Pivot Table", I copied the formula "=GETPIVOTDATA("Sum of "&$B6,'Pivot Table'!$A$3,"Year",D$3)/1000" to the column of 2016. Instead of getting the answer, I got #REF instead. Why does this happen?
Hi Longyin,
Could you please send me a screenshot of your function and the sheet? I'll be happy to help! Please include the formula bar too.
Best,
Ned
Also, I found that 2_8_getpivotdata-before(excel file of this lesson) contains all the answers while 2_8_getpivotdata-after(also excel file of this lesson) does not
Hello Longyin,
I don't see any apparent reason why your function wouldn't work. The only thing i can think of is if 2016 is not included in the pivot table you're extracting from. It needs to be ticked there.
Thanks for the heads up about the files.
Best,
Ned
I also have come across the exact problem here.
Hi, I have the same problem as described above.
What I've also noticed the data in exercise file is different than the one presented in the video. What's more, file name ,,before" should be named ,,after", as it contains all data filled.
Hello. I also had the same problem but what I noticed is copying the Years from the Pivot Table Sheet makes all the difference.
I could be wrong, but I noticed that the "Gross Profit per unit" and "FDM per unit" referencing is wrong in the getpivotdata_after file.
It reads Gross Profit per Unit=Cost of goods sold/Volume and FDM per Unit = Gross profit/volume.
Yes,what you uncovered can be an issue. I believe I have mentioned this in the lesson. If you receive an error when creating the GETPIVOTDATA function - copy the year from the pivot table and use the copied cell to link to. This issue has to do with the specific formatting pivot table cells have. If the cell doesn't have this formatting, Excel is unable to read it and displays an error.