Array formula disabled every time spreadsheet is closed

I have an array formula in a spreadsheet that appears to be disabled every time I close and then reopen the spreadsheet. The formula in the cell is intact, but I need to reapply the curly brackets, using CTRL-SHIFT-ENTER, to the formula every time I reopen the spreadsheet for the formula to work.

The formula is:

Any thoughts on how to prevent this?

Who is Participating?
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
I assume this is Barry's array formula for the "..of-unique-book-IDs.xlsm" file.

I added that formula, saved the file locally, and re-opened without any issue.  I've never heard of this behavior before.  There's no chance the formula is in column D is there?  If so, the Worksheet_SelectionChange event might be affecting it.

AndreamaryAuthor Commented:
Thanks, Glenn.

While it wasn't the formula in Column D causing the issue (I am using the array formula in a different spreadsheet), your comment triggered some experimentation by placing the array formula in different locations on the spreadsheet. When I inserted a new row above the existing data and placed the array formula there, it was retained after closing, then opening the spreadsheet again. So I suspect other formulas or VBA might have been interfering...unfortunately I don't have enough background to decipher the source.

Thanks for steering me in the right direction!

Glenn RayExcel VBA DeveloperCommented:
I'm glad I helped "steer you", but I don't deserve the points for that!  You're welcome to just close the question.

If there are other sheets in regards to this issue, then again, I'd look for Worksheet_Change,  Worksheet_SelectionChange, or Worksheet_Activate events that might update cells in/around your array formula

All Courses

From novice to tech pro — start learning today.