Is there an if statement to test for worksheet visibility?

[spurs]

Is there a way to use a worksheet formula (not in VBA) to test if a worksheet is visible.

For example something like

If worksheet A is visible then = 'A'!B2

but as an equation to put in a cell?

 

[mole999]

what is it you are trying to achieve ?

 

[spurs]

I have a worksheet named "A" , "B" and "C"

Only A or B will be visible at once. C is always visible

If worksheet A is visible than I want to transfer the value in cell B2 from worksheet A to worksheet C cell B2
If worksheet B is visible than I want to transfer the value in cell B2 from worksheet B to worksheet C cell B2

 

[ParamRay]

I don't think you can check for sheet visibility using a standard worksheet formula.

You could, however, use a custom VBA function that works exactly like a worksheet function:

Public Function ISVISIBLE(SheetName As String)

' Returns TRUE if the given sheet
' is visible or FALSE otherwise

  Dim x As Object
  Application.Volatile
  
  On Error Resume Next
  Set x = Application.Caller.Parent.Parent.Sheets(SheetName)
  If Err.Number <> 0 Then
    ISVISIBLE = CVErr(xlErrName)
    Exit Function
  End If
  
  On Error GoTo 0
  If x.Visible = xlSheetVisible Then
    ISVISIBLE = True
  Else
    ISVISIBLE = False
  End If

End Function

 

[shg]

Or similarly,

Public Function IsVisible(r As Range)
  Application.Volatile
  IsVisible = r.Worksheet.Visible = xlSheetVisible
End Function

E.g., =IsVisible(Sheet1!A1)

Hiding a sheet doesn't trigger calculation, though, so it won't automatically update when a sheet is hidden, despite being volatile.