We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [google-sheets-named-ranges]

For questions involving issues with Named Ranges (a group of cells with assigned custom name) in Google Sheets.

Filter by
Sorted by
Tagged with
0
votes
2answers
39 views

gscript “getValue()” returns “#VALUE!” error

Problem My spreadsheet displays a number in the ReturnOnInvestment range, not the #VALUE! error. I checked analysisSheet.getRange("ReturnOnInvestment").getA1Notation() to make sure I'm not confused ...
0
votes
1answer
543 views

How to reference a dynamic range in Google Sheets Script?

I have a sheet where the filter originally started on row 2, so my range was A2:J. As I've added and deleted rows/columns, my range has changed and it's still changing as I edit the sheet. Is there ...
0
votes
0answers
20 views

Auto Increment a Named Range in Google Sheets

I am working on a bidding calculator and would like to be able to automatically increment a Name Range once I finalize the formulas once. For instance, there are 15 cells per item row with formulas ...
1
vote
1answer
287 views

Google Sheets count columns in a merge

Is there a formula function in Google Sheets to count the number of columns in a merge? I could assign a named range to the merge to make it easier.... For instance: see the highlighted merged cells ...
3
votes
3answers
570 views

Is it possible to have Named Constants in Google Sheets

Excel has a notion of defined names which can be used to define constants/literals. Does GSheets allow this? You can see this in action in the following Excel screenshot: . Cell B2 has the formula ...
1
vote
1answer
1k views

Named range in Data validation

I am having trouble getting a named range to work in the data-validation criteria selection. Given that my named range is "ContactRecord" and the rangeid=1234567890, in the data-validation criteria ...
1
vote
1answer
95 views

Is there a way to display named ranges with a space?

Sample spreadsheet here. In the spreadsheet, there is a dependent drop-down list in the first column, which automatically populates a data validation for the second column using the named range from ...
0
votes
1answer
821 views

View all validation rules

I've just created Data Validation for the named range. How can I edit the validation? I tried to click Data Validation... on the range, but there is a new validation rule, but not existing for the ...
0
votes
1answer
724 views

How to use calculated fields to pull up “Grand Total” in Google Sheets?

I have a grand total value that I want to use in my calculated field. Instead of using a specific number like =sum(impr./919), I want something like =sum(impr./"grand total") and that the grand total ...
0
votes
1answer
208 views

How to name a range so that it doesn't automatically change when rows are added in between

Is there a way to name a range so that it doesn't automatically change when rows are added in between? Ex: Range1 = 'Sheet1'!K5:K36 Add a row between rows 6&7 and now this automatically changes ...
0
votes
1answer
74 views

Formula for adding sum total from multiple sheets for a specific name

I am stuck in terms of how to set a formula to get the sum total for a specific "name" from multiple sheets.
2
votes
1answer
746 views

Is it possible to create a Named Range that is the union of two other Named Ranges?

Say I have two named ranges, team_a_coaches team_a_players Is it possible to have another named range, team_a and define it as the union of the first two named ranges? The goal would be that if I ...
1
vote
2answers
183 views

Display the name of a cell in a worksheet

Excel has a little box on the upper left corner that displays the name of the cell. Does Google Sheets have a similar capability? I've named cell B5 "hours" and cell B6 "hrlyrate" so I want to be able ...
3
votes
2answers
3k views

Google Sheets Query(Importrange) with named range?

I am looking for a way to query an imported range using named range. Something similar to this, however I cannot makeit to work Sheet ID#A has a column J whose named range is "Signed" that may ...
1
vote
2answers
1k views

Use named ranges to dynamically add rows in google spreadsheets, while maintaining conditional formatting in newly added rows

I have a table that looks like in Google Spreadsheets: Some numbers in this table are calculated upon a range of cells (for instance, the total in the last row). If I add a new row to this table, I ...
5
votes
4answers
9k views

Can Google Sheets have a discontiguous named range?

I have a data set with multiple values that are not adjacent. I wish to make a named range of these values. Simplified example data: A B 1 ABC 536936062976506 2 XYZ ...
6
votes
3answers
3k views

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 ...
2
votes
1answer
67 views

Named Range insert by mouse

In Google Sheets, when I start to enter a formula and want to insert the reference to a cell, I just click on that cell and get the reference by row and column. However, if the cell is the part of ...
1
vote
1answer
60 views

Toggle named range window with a button?

I would like to know if it would be possible to toggle the different features in sheets with a script. What I am trying to do is to basically create a button that will show the Named Ranges side ...
7
votes
2answers
18k views

Give a name to a cell in Google Sheets

Excel has a little box on the upper left corner that displays the name of the cell (for example A8, C20, BC104, ...) and this name can be changed to a string of choice (like Force, mass, acc, ...) so ...
2
votes
4answers
2k views

Return name of range as string in Google Sheets

I have a range named apple in Google Sheets. I thought a method like getRangeName("apple") would exist. I want to get the names of many ranges and set them as the header for the columns of a new sheet....
1
vote
1answer
860 views

How many named ranges can a single Google Spreadsheet have?

Is there even a limit to the number of named ranges that you can create within a single Google Spreadsheet? It's rather cumbersome to add a new one, so I would prefer to not just open a spreadsheet ...
4
votes
3answers
8k views

how do I get named ranges in Google Spreadsheets to be more reliable/current?

I'd like to pull values from a named range into a calculation. I have this function: function getNamedRange(n){ SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n); } Seems pretty simple. I ...
9
votes
4answers
5k views

Is it possible to use named ranges in Google spreadsheet queries so that the columns references are kept up to date?

Using Google Spreadsheets, you can write queries. However, if you have column letters in quotes, then they aren't updated as column order changes. Is there a way to write these queries so they don't ...