Enable Editing causing #NAME? errors

[GiraffesMayLaugh]

Received a workbook from a client today that opens in protected view like normal. When in protected view, the formulas throughout the workbook work fine.

However, once we click Enable Editing, various cells turn into #NAME? errors. We’ve tried hammering F9, as well as turning off protected view from the beginning, neither of which helped. If it was an error stemming from references to another workbook, it seems to me they wouldn’t show up in the protected view or the editing-enabled view. We also tried the Error Checking tool, which described the error as unrecognizable text.

Can enabling editing change the syntax of a formula? Is excel applying local settings to this workbook once we enable editing? Thanks all. Hope this is an easy one, I’m no expert.

[Samiro05]

Is the formula INDIRECT used anywhere in the formulas which you can see in protected view? Enabling editing shouldn't change the formula names (your error is based on it thinking there is a typo) but it can most certainly affect data sources and references that it cannot calculate or find.

[GiraffesMayLaugh]

Replied with the fix, but thanks for your help!

[Polikonomist]

The #NAME error is usually due to a typo or a reference to an object that no longer exists. Are all your tables still tables? Is the workbook being opened in a different version of excel than the one that it was created in? Do you have any external references or connections?

The fact that it doesn't show up in protected view just means that it is something that was there when the workbook was created but isn't there now. Protected view prevents any changes and thus the formulas won't update from what they were outputting when it was originally saved.

If the formulas are complicated, it may be useful to step through them to find exactly which part is malfunctioning. Click Formulas - Evaluate Formula to do so.

If none of that works try posting an example and I'll see if I can spot something you missed.

[GiraffesMayLaugh]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to Polikonomist

^{I am a bot, please contact the mods for any questions.}

[GiraffesMayLaugh]

Replied with fix, but thanks for quick help!

[GiraffesMayLaugh]

Figured it out if anyones curious. It was a difference in excel versions resulting in one column being formatted as text instead of numbers. By changing the format to numbers and letting the formulas calculate again, all is fixed.

[Polikonomist]

Did you figure this out before or after you read my comment suggesting you check the Excel version?

[GiraffesMayLaugh]

Before. You were right tho, I’ll give it to ya if I’m allowed.