SheetName – This is the text which you want to test if it exists as a worksheet in the current workbook.
What It Does
This formula will test if a sheet exists in the current workbook and return TRUE if the sheet exists or FALSE if it does not exist.
How It Works
“‘”&SheetName&”‘!A1” will create a text reference to cell A1 in a sheet with the name SheetName. In our example “‘”&”My Sheet”&”‘!A1” creates the text string ‘My Sheet’!A1 which references cell A1 in sheet ‘My Sheet’ if it exists. INDIRECT will then return a reference specified by this text string. ISREF will then return TRUE if this is a valid reference and will return FALSE if it’s not a valid reference because the sheet doesn’t exist.