Questions tagged [google-sheets-named-ranges]
For questions involving issues with Named Ranges (a group of cells with assigned custom name) in Google Sheets.
33
questions
0
votes
0
answers
304
views
Data Validation using Named range from Apps Script
In Dec22 Google changed how data validation is maintained [Release note].1
I have thousands of cells using different ranges for validation, and it's now impossible to maintain. I want to change them ...
1
vote
0
answers
19
views
Unlimited range for plot in Sheets
I would like to create a plot whose range extends as new columns of data are added.
I have read that dynamic named ranges are not available in Sheets, and that named ranges are not allowed in plot ...
- 41
0
votes
1
answer
43
views
Help on determine a range of ID's
I would need some help to determine the range of specific id's, like in the picture below the output I would need is in column B, that ID is from A1 to A6. Anyone has any idea to determine this ...
0
votes
1
answer
444
views
Find cell in which named range is defined in Google Sheets
In Google Sheet it's possible to use named ranges to give names to cells, for instance I can call cell A1 as "mass", and reference it in future formulas.
After a while I often forget in ...
- 118
0
votes
1
answer
149
views
Find given time and date in named range
I have a Google Sheet with my wife where we both enter our upcoming events. They are included in named ranges and passed to the main sheet where the information is displayed. For example, I enter in ...
- 1
0
votes
1
answer
381
views
Auto generate named range when a cell is filled
I'm interested in auto generating named ranges for the sake of easy variable creation for formulas.
I generally use a tab called CONSTANTS while building calculators to help keep the data clean and ...
-1
votes
1
answer
4k
views
How to select multiple individual cells on Google Sheets on my Android mobile phone?
How to select multiple individual cells on Google Sheets on my Android mobile phone?
Example:
On Windows it is possible by using the CTRL button + left mouse button click, but I did not find any ...
- 235
0
votes
1
answer
159
views
Sheets - Custom Data Validation Combobox
I am having a problem with making my own custom data validation combobox, here is what I am having a problem with:
Sheet (Hardware) Column A Column S:
Column A = Asset Tag
Column S = is Owned? Yes/No
...
- 503
1
vote
1
answer
49
views
Find-Replace with discontinous ranges
I am editing a Google Sheet with multiple peers. I need to do a Find-Replace on just my rows, say, rows 1:38 and 197:222. (Not permitted to sort the data.) I see many examples on the web of doing this ...
- 11
1
vote
1
answer
149
views
How to Import or Query a Range from Google Sheets into Google Docs?
I'm using Google Docs, the word processor, and am seeking to import a named range sourced in Google sheets into my Google Docs file.
Do the field code feature or any other feature in Google Docs ...
- 227
0
votes
2
answers
1k
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 ...
- 299
4
votes
1
answer
10k
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 ...
- 489
1
vote
1
answer
2k
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 ...
- 579
5
votes
3
answers
8k
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 ...
- 155
4
votes
1
answer
9k
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 ...
- 153
1
vote
1
answer
2k
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 ...
- 13
6
votes
2
answers
9k
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 ...
- 171
1
vote
1
answer
3k
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 ...
- 11
0
votes
1
answer
252
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
1
answer
123
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
1
answer
2k
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 ...
- 121
1
vote
2
answers
590
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 ...
- 19
4
votes
2
answers
6k
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 ...
- 579
2
votes
2
answers
2k
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 ...
- 131
19
votes
5
answers
41k
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 ...
- 1,202
18
votes
3
answers
8k
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 ...
- 365
2
votes
1
answer
83
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 ...
- 149
1
vote
1
answer
98
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 ...
- 19
19
votes
2
answers
39k
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 ...
- 451
3
votes
4
answers
4k
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....
- 111
3
votes
1
answer
2k
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 ...
- 157
7
votes
3
answers
16k
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 ...
- 417
10
votes
5
answers
9k
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 ...
- 273