4

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.

4

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.

  • note that the local ranges created this way cannot be edited any more. – törzsmókus Jul 3 '17 at 15:27
3

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.