CODEWITHSUNDEEP
google-sheets
Roberto Tognelli
Roberto Tognelli

Reputation: 93

How to test if a sheet exists in Google Sheets by formulas

Which is the best way to test if a sheet exists or not in a Google spreadsheet, only using formulas (by a script it is easy, but I need speed!)?

For example, using =error.type('mysheet'!A1) gives error 4, but I am looking for something more specific... (error 4 could mean also something else...)

Many thanks

Upvotes: 3

Views: 5191

Answers (1)

user3536211
user3536211

Reputation: 854

I'm using =IF(ISREF('Nov 17'!BT39), 'Oct 17'!BT39, "") to determine if a sheet exist. Maybe that will help.

Checks whether a value is a valid cell reference.

My sheet time recording has the cell F5 (="October 2017").

Maybe you need to build your sheet name from a string. If so, use INDIRECT to build the sheet path. =IF(ISREF(INDIRECT("'" & LEFT(F5, 3) & " ''" & Right(F5, 2) & "'" & "!BT39")), TRUE, FALSE)

That gives me that path to Oct '17, my reference sheet, which I want to check if exist or not.

Greetings

Upvotes: 8

Related Questions