Array formula disabled every time spreadsheet is closed

Posted on 2014-08-14
Last Modified: 2014-08-14
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?

Question by:Andreamary
    LVL 27

    Accepted Solution

    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.


    Author Closing Comment

    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!

    LVL 27

    Expert Comment

    by:Glenn Ray
    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now