Something weird happens with a workbook that I inherited. When I go into the info tab, it says there are one or more sheets in the workbook "data" that have been locked. I put in the password which it accepts, however, when I go back to the workbook there is no data tab visible, and it says nothing is hidden. There are no queries or links visible either.

Furthermore, I have a formula that references table "FY" and column "Current FY" ="Jul-"&MID(@FY[Current FY],3,2), but nowhere can I find a table called "FY". This table doesn't appear in the Name Manager. It appears in the Name Box drop down list, but when I select it, it does not take me to the table?

What am I missing or what could be happening?

CodePudding user response：

This was a case of a Sheet being set to VeryHidden in the Sheet "Visible" property. To my knowledge, VeryHidden can only be set and unset through VBA or the Properties window in the VBA editor. A VeryHidden sheet will Not be listed when rightclicking any Sheet Name Tab to choose the Unhide option, as opposed to a Hidden sheet, which will be listed.

To Unhide a VeryHidden sheet open the VBA Editor (Alt F11), and in the Project browser window, then inside Microsoft Excel Objects, look for the sheet(s) that don't appear in the Excel. Select that sheet, and in the Properties window (F4) find the "Visible" property. The dropdown there will allow you to change it to one of 3 values: 2- xlSheetVeryHidden, 0- xlSheetHidden or -1 -xlSheetVisible

For more info google: Excel xlSheetVeryHidden or, a great resource is https://www.ablebits.com/office-addins-blog/2017/12/20/very-hidden-sheets-excel/