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:
{=SUM(IF(ISNUMBER(MATCH(ROW(INDIRECT("1:10000")),IF(RIGHT(S2:S1000,3)<>"N/A",B2:B1000+0),0)),1))}

Any thoughts on how to prevent this?

Thanks,
Andrea
AndreamaryAsked:
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.

-Glenn
0
 
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!

Cheers,
Andrea
0
 
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

Regards,
-Glenn
0
All Courses

From novice to tech pro — start learning today.