Need a formula to tell me that a certain worksheet exists

Posted by Veronica on February 28, 2001 1:59 PM

I need a formula to make reference to a sheet name not a cell; I mean when a sheet, let's call it Fee2000, exists in my workbook I want to add 2+3, when it does not exist add 500+100. I tried to use Address function but it does not update; when I changed the sheet name from Fee2000 to Fee2001, it did not update to the new name so instead to add 2+3 it added 500+100. In other words I need to make a relative reference to a sheet name. Any help greatly appreciated.

Posted by David Hawley on February 28, 2001 3:07 PM

Hi Veronica

Try this Custom function I have written. To use it Push Alt+F11 then go to Insert>Module and paste in the code. Push alt+Q and save.

Push Shift+F3 and scroll down to "User defined functions" and select to "SheetExists".

You can use by either selecting the sheet name tab or by typing it. It will look something like:

=SheetExists("Sheet2!")

Function SheetExists(SheetName As String)
Dim Wsht As Worksheet
Application.Volatile
On Error Resume Next
If Right(SheetName, 1) = "!" Then
SheetName = Left(SheetName, Len(SheetName) - 1)
End If
Set Wsht = Sheets(SheetName)
If Not Wsht Is Nothing Then
SheetExists = 2 + 3
Else
SheetExists = 500 + 100
End If

End Function

Any good

Dave

OzGrid Business Applications