A variation of solution: SUMPRODUCT when you want to use all rows in column

[Android]

Thought I'd post the solution I finally used, which is a variation on your
suggestion.

To avoid using a hardcoded max row number, I used:

name_1 = OFFSET('DataSheet'!$A$2,0,0,COUNTA('DataSheet'!$A:$A)-1)

It appears to work.

If I understand OFFSET correctly, this selects rows 2 and counts down &
selects max # of rows (which includes row 1) minus 1.

Regards,

Android.

 

[Harlan Grove]

Thought I'd post the solution I finally used, which is a variation on your
suggestion.

To avoid using a hardcoded max row number, I used:

name_1 = OFFSET('DataSheet'!$A$2,0,0,COUNTA('DataSheet'!$A:$A)-1)

It appears to work.

...

Note that it only works when there are no blank cells in column A. If, e.g., you
had data in cells A1:A20 except for A5 and A12, which were blank, then

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

would resolve to A2:A18 rather than A2:A20. The robust approach is the array
formula

=OFFSET($A$2,0,0,MAX((1-ISBLANK($A$2:$A$65536))*ROW($A$2:$A$65536))-1,1)

 

[Android]

Good point.

So it looks like the hardcoded number (65536) is needed after all.

Thanks,

Android.