How to Check if an Excel Worksheet Exists by Name Using Formulas
Question details
The user wants to verify if a specific worksheet exists based on a name entered into a cell, and if it does, return a value from that worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A worksheet name is typed into cell A1, and a formula in cell B2 is needed to check if that sheet actually exists within the workbook and pull data from it.
- Observed behavior
- Cell B2 should return the value of cell A1 from the target worksheet if the sheet name is valid, or output FALSE if the worksheet does not exist.
Ensure that the worksheet name you enter in your reference cell perfectly matches the name on the actual worksheet tab, including any spaces and special characters.
Use the ISREF and INDIRECT Functions
Combine the ISREF and INDIRECT functions inside an IF statement to dynamically verify a worksheet's existence without using VBA macros.
The INDIRECT function turns a text string into a valid cell reference. By passing the dynamically constructed sheet name to INDIRECT, Excel attempts to link to it. If the sheet doesn't exist, it typically throws a #REF! error. Wrapping this in the ISREF function intercepts that error, resulting in a clean TRUE or FALSE logic check.
Type the exact name of the target worksheet you want to check into cell A1.
Click on cell B2 (or whichever cell you wish to display the result) to make it active.
Type the following formula: =IF(ISREF(INDIRECT("'"&A1&"'!A1")),INDIRECT("'"&A1&"'!A1"),FALSE)
Press Enter. If the sheet exists, the formula will pull the value from cell A1 of that specific worksheet. If it does not exist, it returns FALSE.
Use WPS Spreadsheet to Seamlessly Check Worksheet References
WPS Spreadsheet fully supports advanced logical and reference functions like INDIRECT and ISREF, allowing you to easily verify sheet names and manage complex cross-sheet data links.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
- 2. Enter the target name: Type the name of the worksheet you are looking for into a reference cell, such as A1.
- 3. Apply the reference formula: In an empty cell, type =IF(ISREF(INDIRECT("'"&A1&"'!A1")),INDIRECT("'"&A1&"'!A1"),FALSE) and press Enter.
- 4. Review the result: WPS Spreadsheet will instantly calculate the formula, returning either the remote cell's data or a FALSE indicator.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error instead of FALSE?
If you only use the INDIRECT function, Excel will return a #REF! error when it cannot locate the specified sheet name. Wrapping the INDIRECT function inside the ISREF function correctly evaluates whether the reference is valid, safely converting the error into a logical TRUE or FALSE.
Do I absolutely need to include single quotes in the INDIRECT formula?
Yes, it is highly recommended. Adding single quotes around the sheet name reference (e.g., "'"&A1&"'!") prevents the formula from breaking if the target worksheet name contains spaces or specific special characters.
Can I use VBA to check if a worksheet exists instead?
Yes, you can create a Custom User Defined Function (UDF) using VBA by iterating through the Worksheets collection to check for matching names. However, using the INDIRECT and ISREF formula method is much easier for most users and prevents you from having to save the file as a macro-enabled workbook (.xlsm).
