1) Formula without VBA for know if a sheet is Visible or Hidden and ...

[jamiguel77]

Hi all
2 questions:

1) Formula without VBA for know if a sheet is Visible or Hidden ?
2) i have 100 sheets, in first sheet named: General, and the rest: 4-00, 4-01, 4-02 and so so on...
in sheet General, have the names of the sheets (A1 to A100)
4-00
4-01
4-02
....

in B1, how to Reffer to sheet A-00 to cell A1? my goal is point to Shhet of the cell A1, A2... not static similar to: ='4-00'!A8 <---

Any Advice?

 

[Logit]

.
Here is one answer to your questions ....

This macro goes in a routine module. When you execute the macro, be certain cell A1 on Overview sheet is highlighted. The macro will create a menu list beginning with the cell that is highlighted.

Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
    If ActiveSheet.Name <> sh.Name Then
        sh.Range("A1").Value = "=HYPERLINK(""[Create-Links-To-All-Sheets-in-a-Workbook1.xlsm]Overview!A1"", ""Menu"")"
        
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
        ActiveCell.Offset(1, 0).Select
    End If
Next sh
End Sub

I don't believe you will be able to determine by FORMULA only, if a sheet state is visible or hidden. But as always ... I'm probably wrong.

 

[MARK858]

1) Formula without VBA for know if a sheet is Visible or Hidden ?

I too can't think of a way to do it purely with formula, the nearest that I can think of is to use an UDF.
Please note that the formula won't automatically update when the sheet is hidden, you need to perform an update.

Place the code below in a regular module

Function ShtVisible(myRng As Range) As Variant
    Application.Volatile
    ShtVisible = CBool(myRng.Parent.Visible = xlSheetVisible)
End Function

and use it in a cell as

=IF(ShtVisible(Sheet2!A1),"Visible","Hidden")

The A1 can be any cell reference on the sheet.

 

[gmc]

I too can't think of a way to do it purely with formula, the nearest that I can think of is to use an UDF.
Please note that the formula won't automatically update when the sheet is hidden, you need to perform an update.

Place the code below in a regular module

Function ShtVisible(myRng As Range) As Variant
    Application.Volatile
    ShtVisible = CBool(myRng.Parent.Visible = xlSheetVisible)
End Function

and use it in a cell as

The A1 can be any cell reference on the sheet.

How do I use a variable instead of manually type the sheet name in the formula: =IF(ShtVisible(Sheet2!A1),"Visible","Hidden")?