Possible to use a variable for a row number in formulas?

English
#1

I currently have the following formula:

=SUMIF($Sheet2.$A2:$A32, TEXTJOIN("|", 1, $Sheet1.N2:N42), $Sheet2.$G2:$G32)

I need to use a variation of this formula (or more complicated ones that follow the same general idea) multiple times for different ranges. So I’ll start with rows 2-32, but then want to have a different one for rows 33-57 and again for 58-65, etc.

I’d like to create a variable for the values “2” and “32” in the example formula above so every time I need to modify it, I can just update “2” and “32” to the new values in one place instead of manually scanning with my eyes for every correct instance of “2” and “32” in the formula. Is there a way for me to do this?

During my searches, I found the INDIRECT function. I can get it to work with the “32” value in the example above by putting “32” in cell Q10 and using the formula:

=SUMIF($Sheet2.$A2:INDIRECT("A" & Q10), TEXTJOIN("|", 1, $Sheet1.N2:N42), $Sheet2.$G2:INDIRECT("G" & Q10))

The problem is, as soon as I try that for “2”, the cell gets the error “#NAME?” and the formula becomes

=SUMIF($sheet2.indirect("A" & Q9):INDIRECT("A" & Q10), TEXTJOIN("|", 1, $Sheet1.N2:N42), $Sheet2.$G2:INDIRECT("G" & Q10))

As far as I can tell, LibreOffice considers “indirect” to be a literal string rather than a function name, and so the cell reference of course becomes invalid. Is there some kind of syntax I can use to indicate otherwise?

#3

Welcome!

Try to continue your search and you will definitely come across the OFFSET() function.

The INDIRECT() function is certainly good and does what it is designed to do. But I try to use it as little as possible. In fact, what will the formula do with this function and the row numbers from cells Q9:Q10? It will take numbers from cells Q9:Q10, convert them to text, concatenate them with strings that represent a sheet, convert the resulting string into a range address… When I imagine how many extra conversions the computer will have to perform, I look for an alternative way.

The trick is that the range $Sheet1.N2:N42 can be obtained using =OFFSET($Sheet1.N1;1;0;42-2+1;1) (or if you know that the first and last row numbers are in Q9:Q10, then =OFFSET($Sheet1.N1;Q9-1;0;Q10-Q9+1;1) )

And if you put in Q10 not the number (index) of the last row, but the number (count) of rows, then the formula will become even simpler

1 Like
#4

Possible alternative without volatile functions:

=TEXTJOIN("|";;INDEX(A:A;Q9;1):INDEX(A:A;Q10;1))
2 Likes
#2

Test with:
INDIRECT("$sheet2.A" & Q9):INDIRECT("A" & Q10)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.