GETPIVOTDATA function

[pauloosorio]

Hi,

I am using WIndows 10 and  Excel from Office 365 for small business.

The function GETPIVOTDATA is returning #REF for some cells of a pivot table and returning the right value for others.

See please in attached file. The problem is clearly shown on sheet "Resumo Provisão Malha".

Cell F22 to F24 - function works properly

Cell F25 and F26 - function returns #REF

Thanks

[#2]

@pauloosorio 

I downloaded your file and it took almost 1 minute just to open it.

When verifying your data I could see that

• you are using all the rows of the tab "Dados MacWin" as range, it its unecessary and also it is terrible for perfomance sake.
• Many informations with unnecessary blank space, for instance the field SArm Descricao "    POSOLIS - MALHAS ACABADAS BRAGA". It begins with 4 space characters. It happens with the others as well. 
• Try to standardise you data, for instance, your formula are showing error because you are looking for the "Armazem code"=1390002, for instance that doesn't exist in your data. The Pivot Table made some magic tricks trying to standardise by its own, but if you check your data it is not there.

Try to clean-up your data by removing unnecessary spaces or special characters. You can use the TRIM() funciont for that >>=TRIM(CLEAN(SUBSTITUTE(J2,CHAR(160)," ")))

Avoid use the whole excel range as data set for your pivot table.

Use the excel feature called Table, so everytime your range increases it is updated on your pivot table.

[#3]

Hi Juliano,

Thanks very much for your help. I'm going to follow your suggestions and i will give you feed back.

Thanks very much

B regards

[#4]

You're welcome.
Dont forget if it helped with your problem to flag it as solved and hit the like button please.

[#5]

Hi Juliano,

I'm new here. I was checking around but didn´t understand were I flag it as solved. Can help?
Thanks. B regards