Tracking worksheet names in functions

L

locutus243

Hey guys, bit of a puzzler for you:-

I am trying to create a formula that is based on worksheet names, such
as 'If a worksheet called 'Tuesday' exists then type 1. Is there anyway
that worksheet names can be used in this way??

Thanks

Mark
 
J

JE McGimpsey

One way, using a User Defined Function:

Public Function SheetExists(sName As String) As Boolean
Dim sTemp As String
Application.Volatile
On Error Resume Next
sTemp = Sheets(sName).Name
SheetExists = Err = 0
On Error GoTo 0
End Function

Call as

=IF(SheetExists("Tuesday"),1,"something else")

or perhaps

=--SheetExists("Tuesday")
 
B

Bernie Deitrick

Mark,

You would need to use VBA to create a custom function: copy the function below into a regular code
module, and use it like so:

=IF(ShtExists("Tuesday"),1,0)
or
=IF(ShtExists("Tuesday"),"Tuesday exists","Tuesday doesn't exist")

HTH,
Bernie
MS Excel MVP


Function ShtExists(myName As String) As Boolean
Dim mySht As Worksheet
ShtExists = False
For Each mySht In Application.Caller.Parent.Parent.Worksheets
If mySht.Name = myName Then
ShtExists = True
Exit Function
End If
Next mySht
End Function
 
D

Dave Peterson

=IF(ISERROR(CELL("address",INDIRECT("'tuesday'!a1"))),0,1)

or if Tuesday were in A1:

=IF(ISERROR(CELL("address",INDIRECT("'"&A1&"'!a1"))),0,1)
 
B

Bernie Deitrick

Dave,

Much better solution. But why not just?

=IF(ISERROR(INDIRECT("'Tuesday'!a1")),0,1)

HTH,
Bernie
MS Excel MVP
 
D

Dave Peterson

What happens if Tuesday!A1 contains an error?


Bernie said:
Dave,

Much better solution. But why not just?

=IF(ISERROR(INDIRECT("'Tuesday'!a1")),0,1)

HTH,
Bernie
MS Excel MVP
Click to expand...
 
J

JE McGimpsey

Very nice. I like this version a bit better, but it's personal taste:

=1-ISERROR(CELL("address",INDIRECT("'tuesday'!a1")))
 
B

Bernie Deitrick

Ahh! Since my spreadsheets never contain errors, it's only natural that I forgot about that
possibility... ;-)

Bernie
 
D

Dave Peterson

I liked it when I stole it from Harlan Grove.



JE said:
Very nice. I like this version a bit better, but it's personal taste:

=1-ISERROR(CELL("address",INDIRECT("'tuesday'!a1")))
Click to expand...
 
Top