Tracking worksheet names in functions
 

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


--
locutus243
------------------------------------------------------------------------
locutus243's Profile: http://www.excelforum.com/member.php...o&userid=12862
View this thread: http://www.excelforum.com/showthread...hreadid=490724

Tracking worksheet names in functions
 

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")


In article ,
locutus243
wrote:

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

Tracking worksheet names in functions
 

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


"locutus243" wrote in message
...

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


--
locutus243
------------------------------------------------------------------------
locutus243's Profile: http://www.excelforum.com/member.php...o&userid=12862
View this thread: http://www.excelforum.com/showthread...hreadid=490724

Tracking worksheet names in functions
 

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

or if Tuesday were in A1:

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




locutus243 wrote:

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

--
locutus243
------------------------------------------------------------------------
locutus243's Profile: http://www.excelforum.com/member.php...o&userid=12862
View this thread: http://www.excelforum.com/showthread...hreadid=490724

--

Dave Peterson

Tracking worksheet names in functions
 

Dave,

Much better solution. But why not just?

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

HTH,
Bernie
MS Excel MVP


"Dave Peterson" wrote in message
...
=IF(ISERROR(CELL("address",INDIRECT("'tuesday'!a1" ))),0,1)

or if Tuesday were in A1:

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




locutus243 wrote:

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

--
locutus243
------------------------------------------------------------------------
locutus243's Profile: http://www.excelforum.com/member.php...o&userid=12862
View this thread: http://www.excelforum.com/showthread...hreadid=490724

--

Dave Peterson

Tracking worksheet names in functions
 

What happens if Tuesday!A1 contains an error?


Bernie Deitrick wrote:

Dave,

Much better solution. But why not just?

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

HTH,
Bernie
MS Excel MVP

"Dave Peterson" wrote in message
...
=IF(ISERROR(CELL("address",INDIRECT("'tuesday'!a1" ))),0,1)

or if Tuesday were in A1:

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




locutus243 wrote:

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

--
locutus243
------------------------------------------------------------------------
locutus243's Profile: http://www.excelforum.com/member.php...o&userid=12862
View this thread: http://www.excelforum.com/showthread...hreadid=490724

--

Dave Peterson

--

Dave Peterson

Tracking worksheet names in functions
 

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

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

In article ,
Dave Peterson wrote:

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

or if Tuesday were in A1:

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




locutus243 wrote:

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

--
locutus243
------------------------------------------------------------------------
locutus243's Profile:
http://www.excelforum.com/member.php...o&userid=12862
View this thread: http://www.excelforum.com/showthread...hreadid=490724

Tracking worksheet names in functions
 

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

Bernie


"Dave Peterson" wrote in message
...
What happens if Tuesday!A1 contains an error?


Bernie Deitrick wrote:

Dave,

Much better solution. But why not just?

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

HTH,
Bernie
MS Excel MVP

Tracking worksheet names in functions
 

I liked it when I stole it from Harlan Grove.



JE McGimpsey wrote:

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

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

In article ,
Dave Peterson wrote:

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

or if Tuesday were in A1:

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




locutus243 wrote:

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

--
locutus243
------------------------------------------------------------------------
locutus243's Profile:
http://www.excelforum.com/member.php...o&userid=12862
View this thread: http://www.excelforum.com/showthread...hreadid=490724

--

Dave Peterson