#REF! When referencing cell in pivot table

zorion

New Member
Joined
Dec 18, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I've created a table from an external .csv file and from this table i've created a pivot table. I now want to use the values within the pivot table to create something else. With other projects I was able to just type = and then click the field in the pivot table. When I do this now I instantly get the #REF! error, there is definitely data in the field. The =GETPIVOTDATA formula is automatically created and correct as far as I can tell...

I tried looking for solutions in a lot of message boards but no solution to my problem found.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,776
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you using custom subtotals?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,776
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Can you post the workbook somewhere for us to have a look at?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,267
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Posting the worksheet would be ideal, in the meantime if you highlight the whole pivot and Ctrl+F > #REF
Does the pivot table itself have #REF in it ?
 

zorion

New Member
Joined
Dec 18, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Sorry I can't upload the worksheet as it has proprietary data. When I try to make an example I am unable to duplicate the issue. I have been trying to further troubleshoot this issue and now found that not all cells that I reference to will result in #REF!, only the ones I want to reference of course... There is no differences in the =GETPIVOTDATA formula, just the name of the column of course. The thing that IS different is that of the ones that result in error there are more than 1 entry in the table (I am looking at the MAX date reported of that particular entry). I have attached a screenshot of my issue
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    72.1 KB · Views: 19

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,267
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are hiding the critical pieces of information required to troubleshoot it.
You at least need the formula in the #REF cell and the column heading.

Did you change the column heading after you created the formula ?
You can try simplifying the heading to say just Phase B and make sure the formula is using "Phase B" and see if that fixed the #REF.
 

zorion

New Member
Joined
Dec 18, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Sorry, having a hard time trying to create a good example without exposing any of the data. I seem to have found the issue now... it turns out there is an ENTER right before the row label, when i remove that label the #REF! issue is gone...
 

Attachments

  • 2.jpg
    2.jpg
    48.5 KB · Views: 34
  • 2.jpg
    2.jpg
    48.5 KB · Views: 34
Solution

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,267
Office Version
  1. 365
Platform
  1. Windows
Does that mean you are ok now ?
If you have to, simply rename the heading in the pivot and just make sure your formula matches that name exactly.
 

zorion

New Member
Joined
Dec 18, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Does that mean you are ok now ?
If you have to, simply rename the heading in the pivot and just make sure your formula matches that name exactly.
Yes, I am very ok now. Strange that the source data contains these ENTERS as I can't find them anywhere. I did rename the heading and remove all ENTERS before and after. Formula's are now working OK everywhere. Hope the ENTERS don't return when I refresh though!
 

Forum statistics

Threads
1,147,962
Messages
5,744,053
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top