Does Google Sheets have sheet-level named ranges?

Question

Microsoft Excel allows sheet-level named ranges. See here for an explanation. Google Sheets does have "named ranges" functionality, but I have only figured out how to use it on a workbook-level scope. Is it possible to use sheet-scoped named ranges in Google Sheets?

I would like to be able to have a named range with the same name in multiple sheets within the workbook and refer to them with '*'!MyRange.

Answer 1

Google Sheets does indeed have Sheet-level named ranges internally, and they are almost fully functional. :-)

The challenge is that as of 16/2/16 they can only be created by Copying a Sheet with an existing NamedRange and then Google Sheets create the parallel sheet-level named Ranges (in a similar way that excel does when you copy a worksheet containing Named Ranges).

Unfortunately you cannot create them through the Named Ranges window or corresponding Script function.

However, once you have created the sheet-level named ranges, they perform exactly as you would expect, being local to the page, or you can use the Global (Workbook) version (where a specific sheet does not have an overriding sheet-level range).

I have created a Google Sheet that shows how you can use Sheet-level named-Ranges both in formulas and Google Script. You can see it here.

I have also posted a more detailed answer in Google Forums.

Answer 2

You cannot directly create sheet-level named ranges, because the app will detect that a named range has already been created with that name.

However, you can work around this by copying a sheet with named ranges from another file (right-click, copy to..., select the destination).

You can end up with duplicate named ranges this way, and they will have to be referenced by sheet, e.g. "Sheet1!name", "Sheet2!name". It's a laborious procedure, but it can be useful.

For example, I keep a sheet of abbreviations for common URLs, etc., named "a". I can copy this to a new project and use the same abbreviations by using the named range "a!base_url" or whatever.

Answer 3

Answer

Google Sheets doesn't include a direct way to handle sheet-scoped named ranges.

Alternative

• Make sure that your sheet names follow the guidelines to name ranges.
• Name your cell ranges using the sheet name as prefix and use an underscore as separator. I.E. Sheet1_data.

Guidelines for how to name a range

• A range name can only contain letters, numbers, and underscores.
• It can't start with a number, or the words "true" or "false."
• It must be greater than zero characters, but less than 250 characters.
• It can't contain any spaces.
• It can't contain punctuation, for example commas, periods, dashes, and exclamation points.
• It can't be evaluated as a range in either A1 or R1C1 syntax. (In other words, don't give your range a name like "A1:B2" or "R1C1:R2C2", which already refer to a specific range on your spreadsheet and which may be misinterpreted as such by Google Sheets.)
• A named range can be renamed, however renaming a range may break formulas or references to the original range name.

References

• Name a range of cells - Google Editors Help