Named Range - unwanted @ and spill blocker show up when reopening a file.

[GeeWhiz7]

Hi Gurus,
I have a defined name referencing a column in a worksheet that is actively calculated.
It is called _5yrDates and is simply Today() - 5 years. Other sheets use this name to grab the same data.

When I close and reopen my file and click on the first cell in the named range, it has an @ added to the name (so now it's @_5yrDates) and #VALUE! in the cell. I can delete the @ and it works, but I would rather it not be there in the first place since it happens 120 more times in other worksheets.

Left is what it looks like after reopening. Right is what it should look like.
Farthest right is another problem that comes up when I reopen the file, one of the dates always reappears and blocks the SPILL even if I delete and save before reopening.

I figure they're related, but don't know what I am doing wrong.

Thanks for the help!
-Will

 

[StephenCrump]

Hmm ... formulae changing, dates reappearing.

Have you got macro code running, e.g. on Workbook_Open?

 

[GeeWhiz7]

That's the weird part, I don't have any workbooks with Workbook_Open VBA attached and none of the other macros run without a button push.
That said, when I save the file (.xlsb) to an .xls the issue does not occur. Since I don't have macros running, then I'm assuming it has something to do with dynamic arrays which .xls no longer handle??

 

[GeeWhiz7]

That's the weird part, I don't have any workbooks with Workbook_Open VBA attached and none of the other macros run without a button push.
That said, when I save the file (.xlsb) to an .xls the issue does not occur. Since I don't have macros running, then I'm assuming it has something to do with dynamic arrays which .xls no longer handle??

Sorry, my mistake in writing dynamic arrays. This file has no dynamic arrays at all. It only has named ranges.
the other part I forgot to mention is the file takes longer to open than normal and the little "calculating" icon at the bottom is madly flashing before it opens.

 

[Joe4]

Are you sure that if you open the VB Editor, and look specifically under the "ThisWorkbook" module, that there is no VBA code in there?

 

[GeeWhiz7]

Definitely there was no code in ThisWorkbook.

I got tired of searching so I deleted all the macros and named ranges that were impacted then remade them by cut and paste and somehow this fixes the problem...for now.

Thanks for the help.