Tracking worksheet names in functions

[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

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


In article <[email protected]>,
locutus243 <[email protected]>
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

[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


"locutus243" <[email protected]> wrote in message
news:[email protected]...
>
> 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]

=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

[Bernie Deitrick]

Dave,

Much better solution. But why not just?

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

HTH,
Bernie
MS Excel MVP


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> =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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > =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

[JE McGimpsey]

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

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

In article <[email protected]>,
Dave Peterson <[email protected]> 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

[Bernie Deitrick]

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

Bernie


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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]

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 <[email protected]>,
> Dave Peterson <[email protected]> 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