Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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

=SUM(INDIRECT("B1:B" & A1))
share|improve this answer
    
Thanks, exactly what I was looking for :-) –  Matteo Feb 6 '12 at 8:07

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

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

See the documentation for more details on usage.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.