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.

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.