Moving things like your data tables, calculations, and the user interface to separate sheets is often a good idea to not confuse your users and give spreadsheets a nice look. However, as you add more sheets, it can get hard to keep track and you might want to check whether a worksheet exists to ensure everything is running as expected. Combining the ISREF and the INDIRECT functions, you can keep better track of your worksheets!
Syntax
=ISREF(INDIRECT(name of sheet that you want to check & "!A1"))
Steps
- Begin by typing in =ISREF(
- Continue with INDIRECT(
- Select or type in the range reference that contains the sheet name (i.e. B3)
- Continue by typing in &"!A1"
- Finish the formula with double parentheses )) and press Enter
How
ISREF
The ISREF function returns a Boolean (TRUE/FALSE) value in respect to its argument, whether it's a valid reference or not. Since a valid reference means that the reference exists, the function can also tell us whether a certain worksheet exists. All we need to do is to point it to a valid cell reference (such as A1) in that worksheet.
Sheet1!A1
'New Sheet'!A1
The ISREF function only accepts references, and typing references for each sheet manually can make this approach cumbersome. To make the formula work dynamically, we're going to manipulate a few strings. If we can create worksheet names in cells or formulas, we can make the check dynamic as well. However, we still need to convert strings into references, and this is where the INDIRECT function comes into play.
INDIRECT
The INDIRECT function returns the reference specified by a text string. It is especially useful for when you want to change the reference to a cell within a formula, without changing the formula itself. For example, formulas below will return references,
INDIRECT(Sheet1!A1)
INDIRECT('New Sheet'!A1)
ISREF & INDIRECT
As a result, ISREF checks whether a reference exists, and the INDIRECT returns the references from standard strings. The combination of two functions can check whether a worksheet exists in our spreadsheet. To create a valid reference string we need to add a cell reference to worksheet name as well. Obviously, cell A1 is a good choice. Also, note that an exclamation mark is needed to separate worksheet name from the cell reference.
=ISREF(INDIRECT(B3&"!A1"))
To make the formula 'special-character-safe', you can wrap the sheet name inside single quotes.
=ISREF(INDIRECT("'"&B3&"'!A1"))
