/docs/community?hl=en
/docs/community?hl=en
12/31/15
Original Poster
Daniel Lieberman

Does Google Sheets have sheet-level named ranges?

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.

Community content may not be verified or up-to-date. Learn more.
All Replies (2)
+Samantha
12/31/15
+Samantha
Hey Daniel,

Welcome to the Google Docs Help Forum.

Unfortunately, there is not currently a feature to the Named Ranges option to have sheet-level named ranges.

If you would like to influence future feature additions like this, I highly recommend leaving feedback by following these steps:


  1. Open a Google Sheet.

  2. Click the Help menu.

  3. Click Report a problem.

  4. Complete the questionnaire.


The product team reviews this feedback regularly and uses it to better improve the product over time.


Best of luck,

Samantha

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

Such Ranges are named SheetName!<range_name>  eg "Sheet2!MyRange" , "Sheet3!MyRange".  See the following image :




Google Sheets creates them whenever you copy an existing sheet with Named Ranges to a new sheet either manually or programatically.   It automatically prefixes the new Range Name with the Sheet name matching the copied sheet. (In the above example I had a named range "AWARD_DATE" on my "Main" sheet, then when I duplicated that sheet and Renamed the duplicates to "Input2" and "Input3" the sheet-level Named Ranges resulted).

However, you cannot CREATE such ranges using the standard Named Ranges window, as it does not allow entry of Sheet Names into the Range designator. :-(

Thus you cannot MANUALLY create a range yourself named Main!MyRange or 'Main'!MyRange etc..

As of 16/2/16, you are also unable to create them programatically using the spreadSheet.setNamedRange(range-name,range) function either as it returns a "The name given to this range is invalid" error.

However, if you are prepared to go the sheet-copying route described above, you can happily REFER to your sheet-level named ranges in both sheet Formulas and Google Script.  eg

 = Sheet2!MyRange * 5  or
 = MyRange + 2 (local sheet)  or 
SpreadsheetApp.getActiveSheet().getRange("MyRange").getValue()



Using Named Ranges for Data Validation

Please note that although a call to getRange(RANGE_NAME) will return the correct data (ie local to the Sheet from which it is called), you cannot specify a sheet-level Named Range as a LIST for Data validation on a cell.

Thus while the following is legal ...












... this is NOT legal













Also, please note that when a sheet with named ranges is first copied Google Sheets adds Single apostrophes ( ' ) around the Sheet Name as follows (extra spaces shown here only for clarity)

      ' Copy of Main ' !AWARD_DATE 

..but when the new 'Copy of Main' Sheet is renamed, it drops the apostrophes eg 

      Input4!AWARD_DATE


It's a pity that this very powerful Excel-like feature (Sheet-level Named Ranges) has to be created in such a long-winded fashion... it will be good if Google ever actually update their UI/Script to allow the creation of such ranges easily ... but don't hold your breath  :-)

Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Expert - Google Employee — Googler guides and community managers
 
Expert - Community Specialist — Google partners who share their expertise
 
Expert - Gold — Trusted members who are knowledgeable and active contributors
 
Expert - Platinum — Seasoned members who contribute beyond providing help through mentoring, creating content, and more
 
Expert - Alumni — Past members who are no longer active, but were previously recognized for their helpfulness
 
Expert - Silver — New members who are developing their product knowledge
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.