Condition based on worksheet visibility? Please help!

In article <1129259244.041680.217800 DeleteThis @o13g2000cwo.googlegroups.com>,
juchelka.shopping DeleteThis @verizon.net wrote:

> I have workbook containing several worksheets (Excel for Mac 2004). I
> would like to create formula in cell(s) on one worksheet based on
> visibility (hidden status) of other individual worksheets. Let's say if
> the particular worksheet is hidden, set value to zero, otherwise set
> value to 1. I know how to approach the formula in general, What I don't
> know is, how to check for hidden status of worksheet (what is the
> attribute?). Any ideas?

I've used this User Defined Function in several projects:

Public Function SheetVisible(ByRef reference As Range) As Boolean
Application.Volatile
SheetVisible = reference.Parent.Visible
End Function

To get the result you want, use

=--SheetVisible(Sheet5!A1)

Alternatively, you could rewrite the UDF to return a 1 or 0, but I
prefer to return boolean values as booleans.

Using a range is better than using a worksheet name, since XL will keep
track of the sheet name if it's changed by the user.

Also, this function will not change until a calculation is triggered on
the sheet with the formula, so you may need to hit F9 to get it to
return the correct value.

Thank you guys! That was all I needed. After realizing there is no way
to do it just with formulas (SUBTOTAL would kinda work for me, if it
would also operate on rows, instead just the columns...) I spent few
hours on Friday and did what I needed to do in VB. I am cleaning it up
now and one thing I will try to do to get rid of specific references...
Thanks JE!

Alex

JE McGimpsey wrote:
> In article <1129259244.041680.217800.DeleteThis@o13g2000cwo.googlegroups.com>,
> juchelka.shopping.DeleteThis@verizon.net wrote:
>
> > I have workbook containing several worksheets (Excel for Mac 2004). I
> > would like to create formula in cell(s) on one worksheet based on
> > visibility (hidden status) of other individual worksheets. Let's say if
> > the particular worksheet is hidden, set value to zero, otherwise set
> > value to 1. I know how to approach the formula in general, What I don't
> > know is, how to check for hidden status of worksheet (what is the
> > attribute?). Any ideas?
>
> I've used this User Defined Function in several projects:
>
> Public Function SheetVisible(ByRef reference As Range) As Boolean
> Application.Volatile
> SheetVisible = reference.Parent.Visible
> End Function
>
> To get the result you want, use
>
> =--SheetVisible(Sheet5!A1)
>
> Alternatively, you could rewrite the UDF to return a 1 or 0, but I
> prefer to return boolean values as booleans.
>
> Using a range is better than using a worksheet name, since XL will keep
> track of the sheet name if it's changed by the user.
>
> Also, this function will not change until a calculation is triggered on
> the sheet with the formula, so you may need to hit F9 to get it to
> return the correct value.