Tab hidden/unhidden formula

[Rowanhf12]

Say I have tabs in excel named 1,2,3,...,10.

Is there a formula to determine which of these tabs are unhidden or hidden.
Example. Tabs 1 and 2 are open. Tabs 3 to 10 are hidden.

Tab Formula result
1 Yes
2 Yes
3 No
4 No
5 No
6 No
7 No
8 No
9 No
10 No

 

[dave3009]

I'm not aware of a native Excel function for this, I think you'll need a UDF. If that's a possibility let me know and I'll put one together.

 

[dave3009]

If a UDF is an option here is one

Function isSheetVisible(sh As String) As String
'Test if the sheet actually exists
If Evaluate("ISREF('" & sh & "'!A1)") Then
    Select Case Sheets(sh).Visible
        'Is it visible?
        Case Is = xlSheetVisible
            isSheetVisible = "Yes - Visible"
        'Is it just normal hidden
        Case Is = xlSheetHidden
            isSheetVisible = "No - Hidden"
        'Is it very hidden
        Case Is = xlSheetVeryHidden
            isSheetVisible = "No - Very Hidden"
    End Select
Else
    'The sheet doesn't exist
    isSheetVisible = "No Sheet"
End If
End Function

Use in a sheet like so

Excel 2007
	A	B
1	Name	UDF
2	Sheet1	Yes - Visible
3	Sheet2	No - Hidden
4	Sheet3	No - Very Hidden
5	Random	No Sheet
Sheet1

Cell Formulas
Range	Formula
B2	=isSheetVisible(A2)

You can test if a sheet exists natively, just not it's visibility.

Regards

Dave