Search code examples
google-sheetsgoogle-sheets-formula

Can an open-ended range column be made dynamic with a Google Sheets Formula?

I have this formula in E4to return the lengths of cells in the given array K4:M

=ArrayFormula(IF(LEN(K4:M)>0,LEN(K4:M),""))

Is it possible to make the M range's end of the given array K4:M 'dynamic' and based on this condition:

If I have range K3:O3 the folowwing values:

3   7   4   0   0

retrieved with those formulas in K3:O3:

=counta(K4:K) =counta(L4:L) =counta(M4:M) =counta(N4:N) =counta(O4:O)

Screenshots:

1st

2nd

How can the M be selected and used as the K4:M range end column based on the value in M3 being the last in the range K3:3 being greater than zero? If possible with a google sheets formula?

In pseudocode it would amount to:

K4:The Last Column Letter With value > 0 in range K3:3 = K4:M

I need it to be dynamic because the input in K4:variableend will vary.

In the given example I put the output cell in E4 on the same sheet for ease of presentation of the problem but in real implementation it will be on another sheet to avoid conflict with range K4:variableend.

I tried using the ADDRESS function to retrieve the M column letter and INDIRECT function to use as reference for the range end in K4:M but was not making progresses.

And I'm not sure how to formulate the "last column greater than zero based on the values from range K3:3 " part.

=Substitute(Address(1,13,4),"1","")

and

=INDIRECT(E1)

I looked for another related question but could only find this one which I could not apply successfully to my case:

Google sheets indirect formula dynamic range building

3rd

4th

Solution

  • Can you try:

    =indirect("K4:"&substitute(address(1,max(filter(column(K3:3),K3:3>0)),4),1,))

    OR

    =let(Σ,K4,
     Σ:offset(Σ,9^9,index(match(,0/(K3:3<>0)))-1))