"Invisible" (but not hidden) sheet tabs

[bobsan42]

Hi all,
Hopefully someone has come across this problem and knows the reason for it.

Some background on the file:
This file of course has a tedious 10+ years of history (maybe more) and at the moment it contains 3063 sheets. Over the years it's been broken down into pieces to keep the number of sheets to a somewhat sensible level and still have potentially necessary information at hand. It's not the best of designs, but it is what it is.
However some recent MSO updates make it slower and slower to work with. After the sheet tabs got their latest design it takes at least 5 seconds to switch between sheets (before it was a split second despite the sheets count).
The XLSX file size is currently 18.20 MB (unzipped - about a 100 MB)

The issue:
My brother called and asked how is this possible - to have an INVISIBLE (but not hidden) sheet tab in excel.

I was stumped. In 30+ years I've never seen this.
The tab is clickable, but not visible, the sheet can be activated.
So I'm thinking a bug, memory issues, corrupted workbook ...

First thing I suggested is to hide/unhide the sheet. As a result the next tab becomes invisible too.
He sent me the workbook. I opened it and things look normal - the tabs for the sheets in question are visible. Did some tests, nothing changed.

I suggested to reopen the file, but he answered that on his colleague's computer the file opens in the same twisted way and reloading the file does not change it.
We're both using Office 365, just his is some enterprise or professional version, don't know exactly which license.
While I was writing this, another invisible tab was reported further into the workbook. But I don't want to scroll through 3000+ sheets and inspect their visibility visually.

The only thing close to this I could simulate is: when I switch to the next sheet to the right - it takes some time to load a few more sheet tabs so for a few seconds it really doesn't show, but eventually it does.

So - any clues anyone?

 

[6StringJazzer]

I have never seen this, and I don't know how to solve it. Excel does not have an intrinsic limit on the number of worksheets; it is limited only by available memory. But 3063 is a lot. Likewise, there is nothing technically wrong with a 100MB file, but it is big.

However, I think that these give a clue:

The XLSX file size is currently 18.20 MB (unzipped - about a 100 MB)

If it's 100BM unzipped, then it's 100MB.

So I'm thinking a bug, memory issues, corrupted workbook ...

I'm thinking possibly a combination of all of these, especially after 10 years.

He sent me the workbook. I opened it and things look normal - the tabs for the sheets in question are visible. Did some tests, nothing changed.

The fact that it works fine on your computer suggests that the file is pushing resource limits in some way. What are the specs on the computer where it works, and the one where it doesn't?

Isn't there any possibility of a redesign? At some point you may need to bite the bullet. Can you characterize this file? Are there formulas on the sheets that refer to data on other sheets? If so what functions are used? Are there any external references? I am thinking that this could be broken up to make each worksheet a separate file, but there is no way to know that for certain without a lot more information.

 

[bobsan42]

Thanks for the reply.
Unfortunately no clue on this strange behavior.

I probably created confusion with "unzipped". The XLSX files are essentially ZIP archives, so you can unzip them. But the size of the XLSX file is 18 MB.
Nothing special about the data - some on every sheet, similar sheets, not many and certainly not complex formulas. Not many, if any, images.
The only peculiarity is - almost each tab is coloured (maybe a total of 7-8 different colors).
I wouldn't imagine my notebook is the most powerful, certainly at least one of the other machines can beat mine on some benchmarks.

Basically redesign has been the solution for about a decade now, I am just afraid this task will come to my desk.

 

[6StringJazzer]

Well, not much detail there. Without more detailed responses to the questions I posed I can't begin to guess, other than my initial vague idea that it's some combination of a bug or a corruption. But I'm not sure what bug or corruption would occur on one machine but not another. Again, without knowing the details of both machines I can't even guess.

18MB is a little big but I've had bigger Excel files than that, so it's just not size alone that is the issue.

I would not expect tab color to be a problem, but you know there is an easy way to find out. You could select all the sheets and set tabs to no color (on a copy of your file. Or write a tiny little bit of VBA to do it.