Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


  • 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
up vote 6 down vote accepted

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

share|improve this answer
Thanks, exactly what I was looking for :-) – Matteo Feb 6 '12 at 8:07
Not tagged for it, but this also works for OpenOffice Calc. – Knyri Oct 30 '15 at 19:54

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


See the documentation for more details on usage.

share|improve this answer
Thank you. INDEX is working with external references (without opening them), INDIRECT isn't. – hellcode Mar 9 at 16:24

Your Answer


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.