something weird happens with a workbook I have inherited. When i go into the info tab it says there is 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 says nothing is hidden, there is no queries or links either. What am I missing or could be happening?

Further to that I have the formula ="Jul-"&MID(@FY[Current FY],3,2) but nowhere can I find a table called Current FY or FY, nothing in named manager but appears in the dropdown and when I select it it does not take me to the table?

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/