INDIRECT Formula shows #VALUE! error when opening the file

[StephenArg]

I have an Excel workbook with no external references and no VBA. There are only 4 worksheets in the file and on one of those worksheets, I use the INDIRECT formula to automate getting a value from a cell in a different worksheet in the same workbook. The formula is looking for a value from within the "V2_front" sheet and it is:

=INDIRECT("V2_front!"&ADDRESS(14,ROW()+$A$13))

The result of this formula is equivalent to: =V2_front!D14

My issue is that, every time I open the workbook, all the cells that have that formula and any that are dependent on it show the #VALUE! error. I then double-click on any of the error cells then press enter to refresh it and all the cells go back to normal again, with everything working as expected. I've noticed the same problem with another (entirely unrelated) workbook, which also uses INDIRECT.

BTW: I'm using Office 367 on a MacBook Pro and have the latest version of Microsoft Excel for Mac (16.54) installed.

Does anyone have any ideas about why this is happening or how to resolve it?

Thanks
Stephen

 

[JEC]

Check if your calculation settings are set to manual. You need automatically.

File -> settings -> formulas

 

[Eric W]

You can also rewrite that formula without INDIRECT:

=INDEX(V2_front!$14:$14,ROW()+$A$13)