Possible to have a formula to show "TRUE"/"FALSE" depending on sheet visibility?

[povictory]

Hello!

Is there a way to have an output in a cell show "True" or "False" depending on if another tab is visible or not visible? I'm looking to leverage that output for some other formulas/conditional formatting, but I only want to do that if another specific tab is visible and available to the user.

Any assistance is greatly appreciated!

 

[Micron]

I think you'll have to use code. Here's one example

Worksheet Formula to show if tab is hidden

Let's say I have a "sheet1" and "sheet2" in Excel. I want cell A1 of "sheet1" to say TRUE if "sheet2" is visible and FALSE if "sheet2" is hidden. I know this can be easily done with VBA, but I am

stackoverflow.com

 

[povictory]

I think you'll have to use code. Here's one example

Worksheet Formula to show if tab is hidden

Let's say I have a "sheet1" and "sheet2" in Excel. I want cell A1 of "sheet1" to say TRUE if "sheet2" is visible and FALSE if "sheet2" is hidden. I know this can be easily done with VBA, but I am

stackoverflow.com

Thanks...I actually found that in a search before posting, but I can't get it to work for me. The formula returns a #VALUE! error for some reason.

 

[Micron]

Post what you tried and the function call you used?

 

[HongRu]

Thanks...I actually found that in a search before posting, but I can't get it to work for me. The formula returns a #VALUE! error for some reason.

I try it and it works well.
Maybe you have something wrong in the formula.

=if(issheetvisible('sheet2'!a1),TRUE,FALSE)

Do you actually have the sheet named "sheet2" ?

 

[bebo021999]

Assum sheet name "ABC" need to check visible or not

In any cell of active sheet, type

=Visible("ABC")

returns TRUE if visible, FALSE invisible

Code:

Option Explicit
Function Visible(ws)
Application.Volatile
If Not Evaluate("=isref('" & ws & "'!A1)") Then
    Visible = "sheet does not exist!"
    Exit Function
End If
Visible = (Sheets(ws).Visible = True)
End Function

Visible.xlsm

drive.google.com

 

[povictory]

Thanks for the replies to help sort this out! I realized what I was doing wrong - I was using the sheet code in the formula instead of the name in the tab. So I was able to get it to work. However, one issue I'm seeing now is that you actually have to manually trigger the formula to update the formula output. Is there a way to trigger the formula output to update automatically when the sheet is hidden or unhidden?

 

[bebo021999]

How that tab was forced to hide / unhide? manual of by another macro?
If manual hide, use that tab deactivate event ( or active sheet activate event) to trigger to input value TRUE/FALSE into active sheet
If by another macro, add a line to input value TRUE/FALSE into active sheet

 

[povictory]

There's another macro that unhides it when a hyperlink to the tab is clicked from another sheet. Apologies, but could you share how adding the additional line would look in the code?

 

[povictory]

Bump