Referencing a cell in Excel using values from other cells

Question

I would like to know if it is possible to reference a cell in Excel depending on the value of another cell.

Example

• I have a cell, let's say A1 where I have the row number that I want to use (e.g., the content of A1 is 42)

• I want to compute the sum of column B from row 1 to the row corresponding to the number given in A1. Something like =SUM( B1:B<NUMBER_IN_A1> )

Is there any way?

Answer 1

Use & to create the cell coordinates, access them using INDIRECT().

=SUM(INDIRECT("B1:B" & A1))

Answer 2

INDEX is normally preferable to INDIRECT where possible - because you don't have to "hardcode" the column and because it's not (as) volatile.

=SUM(B1:INDEX(B:B,A1))