Not sure exactly where to post this, so I’m posting where most of the undaunted Excel builders are…
Our team was struggling with an issue where some formula-driven cells would show a #REF! error when a workbook was opened.
Trivially changing a formula (e.g. adding a meaningless space at the end) would “jostle” the cells into a recalc that would display proper results. If cells started with an error, were jostled and then the document was saved, the workbook would reopen with the error again.
We learned that there is a known bug related to including a # directly within an INDIRECT() function.
Until Microsoft fixes the bug, the workaround is to change the formula as follows:
=2*INDIRECT("A1#")
Should
=2*INDIRECT("A1")#
Not sure if anyone is struggling with this but thought it might be helpful. 