If you want to check if a worksheet bearing a certain name already exists in a workbook, function ISREF and INDIRECT will be helpful. This article will demonstrate the functions and their usages.
To check if a worksheet name exists in a workbook in Excel using the ISREF and INDIRECT functions, follow the procedure in the example below:
- Click on Formulas on the menu bar in Excel.
- Click on More Functions and point the cursor to Information.
- From the list that displays, click on ISREF.
The dialogue box will display as shown below.
Figure 1: Accessing the ISREF function
In the Value field above, type this formula: ((INDIRECT(B9&“!A1”))). B9 is the cell address containing the worksheet whose existence you are trying to check.
- After that, click OK.
When you click OK, you will be shown the result demonstrated on the screenshot below.
Figure 2: Example showing results from ISREF and INDIRECT functions
Interpretation of the Result
In the example displayed in Figure 2, the column on the left contains the names of worksheets while the list on the right is a true or false statement about whether or not the worksheet name on the left exists in the workbook. A workbook is made up of at least one worksheet. More worksheets can be added, and already existing worksheets can be deleted without altering the chronological numbering of the remaining sheets. Using the ISREF and INDIRECT functions in Excel as described above, each of the names has been checked.
Those that exist in the workbook are marked True while those that do not exist are marked False. This has been achieved by using the formula
=ISREF((INDIRECT(Sheet name &“!A1”)))
The “sheet name” is the name of the worksheet whose existence is being checked. On the screenshot displayed below, you can easily verify the existing worksheets by physically looking at the workbook.
Another way to do this is to simply type the formula above into the cell on the right of the corresponding worksheet name you are interested in checking. Hit the enter key when you finish typing and the result will show.
Whether you use the first method or type the formula directly, you will have to do this against each worksheet name you want to check.
The way the formula works
The ISREF function will display FALSE for an invalid worksheet name but TRUE for a valid worksheet name. In the example above, we are trying to check if a certain sheet can be found in the workbook. We do this by creating a complete reference via the concatenation of sheet names that are listed in the left column with “A1″ and one exclamation mark, giving us this: B9&”!A1″
The INDIRECT function then attempts to assess whether the text is a valid reference. If it is a valid reference, it is forwarded to ISREF which gives TRUE. Where the INDIRECT is not able to generate a reference, ISREF gives FALSE.
The case of sheet names containing punctuation marks and spaces
The formula is adjusted by enclosing the sheet name with single quotes as shown below: