4

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    891048446616931
3    ABC    250538612742584

I wish for B1 and B3 to be part of a single named range, but I can't find a way to do this. Is it possible?

2

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 sheet for your named range.

If you use the right filtering technique (regexmatch...) you can create multiple named ranges from a single data table.

0

The built-in named range feature doesn't support non-adjacent ranges but there are some alternatives.

  1. 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.
  2. Use Google Apps Script to create custom function to be used as named ranges.
    • NOTE: The "problem" with custom functions is that they only recalculate when their parameters change and that can't use volatile functions, like NOW() as arguments.
-2

Sure you can! To define non-contiguous ranges, use an array notation: on the box where you define a range enter:

 {A:A,C:C,E:E} 

This should create a discontinuous range that includes columns A, C and E but not B or D.

In functions like vlookup the named range behaves as if B and D did not exist and A,C and E were next to each other, in other words, a VLOOKUP(value, named range, 2) will select cells belonging to C (it's the logical range's index2.

Selecting like so only works for columns of equal height: can't have a "jagged " non-contiguous range where some columns are shorter.

It's also possible to do so on equally long rows, but that requires a two dimensional array:

 {{b5:5}, {b8:8}, {b10:10}}

Would create a non-contiguous range including rows 5, 8 and 10 from column B onwards, and

 {{b5:h5}, {b8:h8}, {b10:h10}}

Would include 3 rows, non-adjacent to each other 6 columns wide, starting in column b and ending in column H

Again, can't have jagged rows, (starting or ending on different columns even if they total 6 cols per row

  • 1
    I'm afraid this completely eludes me. I'm not sure if it even applies... maybe you didn't quite read the question? Either that or I'm completely misunderstanding your answer. My goal is to have discontiguous cells in the same column, be part of a single named range. I don't see how that's possible with what you've outlined here. I even tried it just for fun, but Google Sheets will not accept notation as you've written here, for the range of a named range. – Jonathan van Clute Oct 1 '17 at 3:08

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.