Go Back

Worksheet name exists

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:

Example

  • 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:

=ISREF(INDIRECT("'"&sheetname&"'!A1"))

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

WHEN USING MOVE OR COPY FUNCTION FOR CREATING A COPY OF A PAGE I GET A MESSAGE "A FORMULA OR SHEET YOU WANT TO MOVE OR COPY CONTAINS THE NAME '_BU65999' WHICH ALREADY EXISTS ON THE DESTINATION WORKSHEET.
Solved by F. H. in 22 mins
Hello, I have a list of values from highest to lowest with different names next to the values. When i do a vlookup of duplicate values i get the same name. Is it possible to lookup duplicate values and if a name already exists in the worksheet it goes to the next name in the report?
Solved by G. H. in 27 mins
I have just switched computers, last week if I moved a worksheet from one book to another, i was prompted with the below question, I no longer see that question, excel just copies the name again, how do i get the prompt back? A formula or sheet you want to move or copy contains the name "MY_MENU", which already exists on the destination worksheet. Do you want to use this version of the name? * To use the name as defined in the destination sheet, click Yes. * To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box
Solved by T. H. in 14 mins

Leave a Comment

avatar