Google spreadsheet formula which takes its cell range values from other cell?

Question

I have a simple formula in A1 cell =sum(A2:A10). Now in a B1 position lets say I put A15 and I would like it to modify a formula in cell A1 to be like this: =sum(A2:A15). So basicly I would like to be able to write a formula like: =sum(A2:A(B1)) which ofcourse does not work but you get what I mean. Apreciate any help with this problem. Thanks.

Hibben

Answer 1

You can use the function INDIRECT (functions overview).

In you case you would place in your A1 cell this formula:

=sum(indirect(CONCAT("A2:A";B1)))

Indirect takes text and turns this into a reference of a cell or range. furthermore CONCAT is used to make the text reference.

Voigt

Answer 2

Just a note - if the value in B1 position is 15 then the response by user254948 is correct. If however the value in B1 position is A15 (as proposed in the original post), the corrected formula would be:

=sum(indirect(CONCAT("A2:",B1)))

Apulia

Demo: web.archive.org/web/20230831184452/https://i.imgur.com/… – Irruption