How To Test If A Sheet Exists

Example

=ISREF(INDIRECT(“‘”&B3&”‘!A1”))

Generic Formula

=ISREF(INDIRECT(“‘”&SheetName&”‘!A1”))

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.

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Related Articles

How To Conditionally Concatenate A Range

This formula will conditionally concatenate a range based on a criteria in another range.

How To Solve a Quadratic Equation

These formulas will give the solutions to a quadratic equation.

How To Select A Random Item From A List

This formula will return a random selection from a given list with each item in the list having an equal probability of being selected.