Disclaimer: For reasons outside the scope of this inquiry, I am trying to learn the reason my workbook is behaving the way it is, NOT looking for suggestions about how I could restructure my workbook to "work around" or "avoid" the issue. Any/all help would be most welcome!
I have a workbook which has been begun performing poorly for me, and which I have stripped down little by little in order to uncover the root cause(s). It is currently saved as .xlsx, with 10 sheets in it. All sheets use defined names, all of whose scope is the entire workbook.
Four of the sheets take about 20 seconds to activate or to execute a change in a cell. The others behave quickly and normally.
- When I delete the named ranges of the slow sheets, they behave normally.
- When I recreate the named ranges from scratch, the sheets become slow again.
- The named ranges of the sheets that take a long time to activate are not used in formulas elsewhere in the workbook.
- The slow sheets have 8, 11, 18, and 22 named ranges, respectively.
- The sheet with the most named ranges by far is not one of the slow sheets. It has 54 named ranges.
All sheets are laid out as follows:
- Defined names begin with "[abbrev]_", where [abbrev] is a one- or two-letter shorthand form of the sheet name
- Defined names encompass entire rows or columns
- A defined name called "[abbrev]_HEADERS" covers row 2
- A defined name called "[abbrev]_CATEGORIES" covers row 1
- Columns containing a header in row 2 have a defined name, "[abbrev]_[headername]"
Some other steps taken:
- Ensured the troublesome file is saved on the local HD as opposed to a remote server
- Updated Office
- Run the document inspector and followed its recommendations
- Tried two different licenses:
- MS Office 365
- MS Office 365 ProPlus
I have had no luck whatsoever resolving this issue. What could be causing this problem?
Thanks in advance!