5

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?

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

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

1

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.
  3. (UPDATE) Another alternative is to create an array of range references and use the Google Apps Script methods to handle range lists.
  • +1 for option 1 here. At minimum, you could put in some cell somewhere in another sheet the formula ={B1;B3} and that would construct the range that you asked for. But it's more likely you'd do something like =FILTER(A1:B3,A1:A3="ABC") to dynamically generate the range you want from your data. – Jangari May 16 at 2:33
  • @Jangari Thanks for your feedback. By the way, I just added a third alternative. – Rubén May 16 at 2:35
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,"ABC")),filter(B1:B4,REGEXMATCH(A1:A4,"XYZ")))

Would allow you to call your data using the ABC and XYZ names.

-1

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
  • Thanks, this really helped me. The answer doesn't address Named Ranges but rather, addresses a disjoint range specifier. I used this syntax to select for instance =FILTER( { Sheet!A:A, Sheet!C:C }, Sheet!B:B=false ) so that I do not have to deal with the B column in the results :) – Douglas Held Jul 6 at 14:51

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.