Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formulas showing blank

Copper Contributor

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:

 

Screenshot (232).png

At some point data disappears:

Screenshot (230).png

Thanks

3 Replies
It might be a good idea to remove the IFERROR function from the formula so you can see what error is returned.

@Jan Karel Pieterse 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.

 

I would scrutinize the GETPIVOTDATA formula and double-check if none of the filter criteria are missing from the pivot table.