5

I find one of the most useful aspects of Excel's new(-ish) Dynamic Arrays is that the # operator lets me refer to a range of cells where the number of rows is unknown (and liable to change). Is there anything equivalent in Google Sheets?

For example (view here; download here). Suppose I have a simple two-column array of raw data, containing names in the first column and ages in the second. I then create a new array using FILTER(), where I let the user of my spreadsheet choose an age by which to filter the raw data. I then do some further operations on that FILTER'ed array.

Because I cannot know in advance which ages the user will choose as filtering criteria, I cannot know in advance how many rows the resulting FILTER'ed array will have, which could be a problem. However, with Excel, I don't need to know because I can use the # operator to access the FILTER'ed array without knowing the size. Or I can use ROWS(G2#) -- assuming that's the array top left cell -- to get the number if I want it.

Does Google Sheets have anything equivalent?

To be clear: I can come up with all manner of ways of achieving, in Google Sheets, pretty much the effect I get in Excel. But they're cumbersome and time consuming. What I'm looking for is something as simple and easy as Excel's # operator that I may simply have overlooked.

Is there?

player0
  • 124,011
  • 12
  • 67
  • 124
tkp
  • 197
  • 7

3 Answers3

4

in google sheets you do:

G2:G

or if you want it limited then:

G2:G100

this can be used in formula as a range. tho if you want to use it standalone you do

={G2:G}

or:

=INDEX(G2:G)

or:

=ARRAYFORMULA(G2:G)

in your scenario you would use:

=FILTER(A:A; B:B>=18)

or you can use sql query like:

=QUERY(A:B; "select A where B >= 18")
player0
  • 124,011
  • 12
  • 67
  • 124
  • Wow, that rowless notation for the second part of the range is pretty much it! Thanks. Although, what about a 2D array? In Excel, I could use G2# to refer to an array, with G2 as top left cell, of unknown number of rows *and* columns. – tkp Jul 16 '21 at 00:10
  • 1
    @tkp for unknown columns you could use `G2:1000`. for unknown columns and rows you will need to indirect it like: `INDIRECT("G2:"&ADDRESS(ROWS(A:A),COLUMNS(1:1)))` – player0 Jul 16 '21 at 05:27
0

One workaround is to use the formula in count and sequence. Example:

=Sequence(CountA(Filter(answer with multiple rows))

This would give a dynamic count of whatever your filtered list is.

Jeet Shah
  • 1
  • This would require reference to a known range which runs contrary to the problem being described, no? Excel's spill range overcomes that problem. – lb_so Aug 12 '23 at 10:37
0

I don't think this question is answered, primarily because none of the solutions come close to the functionality of the single cell excel spill notation, especially when complex operations are occurring on the array and longer and longer formulas become difficult to follow.

Additionally, and perhaps more critically, the G2:G style solution above produces incorrect results if ANYTHING unrelated to the G2 array is entered beneath the last array entry in column g. This vastly increases the amount of space required to manipulate large arrays as no operations can occur under the original array when this method is used. The solution proposed must come with this significant caveat.

lb_so
  • 84
  • 5