GETPIVOTDATA - giving me a major headache

So, I have a workbook with several tables, pivot tables, and pivot charts. The main table is for data entry, there is a pivot table that summarizes this data into weekly totals, and a second table that looks at the pivot data to do some additional calculations and charting. The second table uses the GETPIVOTDATA function, and it is here that the problem starts...it will #REF for various items (sometimes 3, sometimes 9); and I can't for the life of me figure out the rhyme or reason as to which numbers are recognized and which are not recognized. Let's say that item "3" is generating a #REF, but the pivot table has data for this item. Doing a "dummy check" and typing "=" and clicking on the cell in the pivot table gives a #REF, but when you select the formula and change the ITEM ("3") to the cell reference (A11) it works.

Makes me think that the issue is formatting but I looked at the source data and they are whole numbers.

GRRRRR! Thanks in advance!

Thanks for the responses. DILIPandey, I am not sure I am tracking you – I understand how the function works, and routinely use it successfully. Problem restated - if I select an empty cell, type the equals symbol (“=”) and then click a cell in the pivot table, it should return that value, and auto populate the getpivotdata function. This does not work. The “item” automatically fills in as “3” but yields a #REF. if I change “3” to “A9” (in this case) it will work. So, for some reason, the “3” is not a “3.” The other anomaly is that it is not always the same number(s) that are not recognized – sometimes “3” works and “8” does not.

ScreenHunter_55 Sep. 19 06.40.jpg

JP,
I wish it was that simple. Tried the quotes. See the attached image of item 2, which works without quotes.ScreenHunter_55 Sep. 19 09.00.jpg

I did try the quotes around the 3 ("3"), and it did not work. Additionally, I cannot use quotes because they would break the original formula which uses a named cell from a table (Table26[[#This Row],[Week Number]]). Unfortunately I cannot post the workbook - it is large and the info contained is confidential. In the example the week munber is 3.

That option is not available in the values area, and it gives the error for the row label. If I change the row label to a date, it will let me group - but then the getpivotdata function would have to be redone. This would introduce a new problem, in that the workbook has to work with any start date.

C63

I went to the source data and entered the number 3 in place of the formula that figures out the number and it did not work. The integer function should return a whole number every time...Also cleared the cache and deleted / recreated the table.

=IF(Table13[[#This Row],[Calendar Week number]]="","",IF(Table13[[#This Row],[Entry Date]]=MinBTDate,1,INT(DAYS360(MinBTDate,Table13[[#This Row],[Entry Date]],)/7)+1))

Thanks for trying, wish I could post it.

Unfortunately the workbook is so interlinked it would be impossible to remove any sheets. I could fill it with dummy data, but would still have issues with intellectual property.

That being said, I know first hand how difficult it can be to troubleshoot something you can't see or work with.

On the positive side, I added Round() in front of INT() and now it works...now sure why the integer function was not returning a whole number, but hey I am half way out of the woods.

C63