Forum Discussion

NathanKoalaCherries's avatar
NathanKoalaCherries
Copper Contributor
Dec 12, 2023

Formulas showing blank

Hi, 

I am having an issue with a large shared spreadsheet that is accessed using excel on the web. It has a link to a power bi dataset that makes 4 pivot tables that are then referenced by formulas in the rest of the sheets. Where we refresh all data everything works but that moments later the formulas referencing the pivot table are blank. Data is still in pivot tables where I am referencing it. Automatic calculations is turned on. Multiple people are potentially editing this sheet at the same time. Anyone know how to fix or had a similar issue?

 

After refresh all:

 

At some point data disappears:

Thanks

BI & Data Analysis
Excel
Excel for web
Formulas and Functions
office 365
Power BI
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Dec 12, 2023
    It might be a good idea to remove the IFERROR function from the formula so you can see what error is returned.
    • NathanKoalaCherries's avatar
      NathanKoalaCherries
      Copper Contributor
      Dec 13, 2023

      JKPieterse That's a good point! It gives a #REF! error but the data I'm referencing is there in the pivot table. Refresh all will get rid of the error and then the error reappears.

       

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Dec 13, 2023
        I would scrutinize the GETPIVOTDATA formula and double-check if none of the filter criteria are missing from the pivot table.