9

You'll want to make use of Named Ranges. In this case, the range of cells is one. Select your cell Toolbar → Data → Named ranges... (or right-click on cell and select Define named range) On the right you will notice a new Named ranges sidebar appear Edit out "NamedRange1" to whatever you prefer to name the cell Double-check the cell reference below your ...


5

This little script will a retrieve named range and make a summation: function namedRange() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getActiveSheet(); var nRange = ss.getRangeByName("budgetItems"); var data = nRange.getValues(); var sum=0; for(var i=0; i<5; i++) { sum += parseInt(data[i]); } sh....


5

It's a kind of tricky, but it is possible with a helper Range and some concatenation. What needs to be done: Create a named range, COLS, to carry the column letters like this: A B C D E ... Do it in a vertical way as shown. Assemble the query string like this: =QUERY( B:J, "SELECT AVG(" & INDEX(**COLS**, COLUMN(J1)) & ") GROUP BY " &...


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 ...


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 ...


3

At this point, I don't think it's possible. So, what to do? If it's simple, you can just do this: C1=B1 C2=B3 then create a named range for C1:C2 This is hard to maintain. A trick I've done to make this easier to maintain. Add a range name column to your source data. In an auxiliary sheet filter the source data by that text in that column Use that ...


3

Is there a way to write these queries so they don't need to be updated every time a column is added or removed? Is it possible to use named ranges in queries to solve this problem? Yes, you could use named ranges instead of built-in column headers but the basic idea is the same as other answers to this an other similar questions in this site ...


2

function getNamedRange(n) { SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n); } The first problem, you have not assigned the result of this chained statement to anything. getRangeByName returns an object that references the named range and must be assigned to something, or its result is lost. Second, you have not returned anything in the function. ...


2

Not to point out the obvious but your function is never returning anything function getNamedRange(n){ SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n); } you need function getNamedRange(n){ return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n); } now var items = getNamedRange("budgetItems"); items.getValues(); should work just fine


2

Per @Rubén's comment, it doesn't appear possible to call up interface items in the way you describe. However, you can make use of the native keyboard shortcuts. To get to Named Ranges on a PC: In Google Chrome: Alt + d. Other browsers: Alt + Shift + d Press n. On a Mac: Ctrl + Option + d Press n.


2

At this time it's not possible to use named ranges to the "Apply to range" setting of Conditional Formatting.


2

IMPORTRANGE only imports range values, it doesn't import named ranges. NOTE: Suopose that the named range Signed is on the same spreadsheet file that your QUERY formula. It still will not work because MID(CELL("address",Signed),2,1) returns the column letter instead of a column number. Replace that formula with COLUMN(Signed)


2

while answer would be yes for cases when you use named ranges in formulas, this is not possible in data validation, because data validation doesn't support custom formulas for input and also doesn't support multi-range inputs. therefore the only workaround would be to use some hidden helping column with a formula: =SORT({team_a_coaches; team_a_players}, 1,...


1

Sure. Use the range object's getMergedRanges() method. Note that it returns an array of Ranges, so if testing a single cell, be sure to [0] -grab the first range returned. Demo with G3 as the cell to test, and getNumColumns() used to detect merge width, per your example. Run it and choose View→Logs to see what it found. function getCellMergeRange() { var ...


1

when referencing a Named range in Data validation, you don't need to include sheet name:


1

No, there is no workaround for getting a named range with space. Range names: Can contain only letters, numbers, and underscores. Can't start with a number, or the words "true" or "false." Can't contain any spaces or punctuation. Must be 1–250 characters. Can't be in either A1 or R1C1 syntax. For example, you might get an error if you give ...


1

Not possible, there is no Data validation preview in Google Sheets where you would see the list of all rules you created (like in Conditional formatting, Protected ranges or Named ranges).


1

You can do this using the indirect function. If cell A1 contains the first row of the range (5 in your case) and A2 contains the last (36), if you are looking for the sum of the range you can use the following formula: =SUM(INDIRECT("'Sheet1'!k"&A1&":k"&a2)) If you add more rows to your range, the formula will not change its results.


1

Google Sheets doesn't include a built-in feature that only displays the range name of the active cell. By the other hand, at this time, the Google Apps Script triggers doesn't include on that is fired when a user click on a cell We could use the toast method to show a message on the bottom right corner of displayed sheet, but it isn't permanent. the ...


1

Use 1 row extra for your color scale: Use F2:F9 ,When you want the color scale only over F2:F8. Since F9 is only "totals",It won't matter. Then when you add a row,Click 8:8 and add a row below/above. This also works for SUM. There's no need for named ranges.


1

I don't know if you can create a named range, but you might be able to treat the data like a named range using filter and regexmatch functions together. So given your data A B 1 ABC 536936062976506 2 XYZ 891048446616931 3 ABC 250538612742584 4 XYZ 730573819217927 Something like this =slope(filter(B1:B4,REGEXMATCH(A1:A4,"...


1

The built-in named range feature doesn't support non-adjacent ranges but there are some alternatives. Use an auxiliary sheet to put together the values of non-adjacent ranges, so we have them together. We could use QUERY, FILTER, use {}notation, etc. Use Google Apps Script to create custom function to be used as named ranges. NOTE: The "problem" with ...


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 ...


1

At this time it's not possible. You should consider to send comments to Google asking to include the features you are asking for. Google Sheets users could send comments to Google from a Google spreadsheet by Open a spreadsheet Click on Help > Report a problem. Fill and submit up the Google Feedback form.


1

That's currently not possible in Google Apps Script. See this enhancement request in the Google Apps Script issue tracker, to make that possible: issue 4245 As mentioned in the issue itself: To subsequent readers: If you are also interested in this requested feature, please click the star next to the issue number.


1

I was able to make 4963 named ranges, before the execution time of the Google Apps Script expired, using this piece of code. Code function namedRanges() { var ss = SpreadsheetApp.getActiveSpreadsheet(); for(var i=1; i<5000; i++) { var r = ss.getRange('A'+i); ss.setNamedRange("test"+i, r); } } Screenshot If you plan on making larger named ...


1

Ruben's answer doesn't solve the problem for all cases because MID(CELL("address",Grades),2,1) fails if the Grades range is moved to a double-letter column like $AA:$AA. In that case, the formula MID(CELL("address",Grades),2,2) is needed. For named ranges that span or abut the $Z:$AA range, this formula becomes problematic. The formula REGEXEXTRACT( CELL("...


Only top voted, non community-wiki answers of a minimum length are eligible