Pass row number as variable in excel sheet

52
votes

Suppose I have:

  1. a value of 5 in B1
  2. I want to pass the number (5) in B1 as a row variable, which will be read in conjunction with column A into another cell (say C1) as "=A(B1)" i.e. "=A5"

How would I do this?

3
excelexcel-formula
Yashpal I rewrote this because it was unclear which is why t.thielemans found it hard to answer. – brettdj
I am sorry, by mistake i rolled it back, and i don't how to revert my action. I am new to this. so it happened by mistake. Can you please do it again or revert back your change over it. – Yashpal Singla

3 Answers

74
votes

Assuming your row number is in B1, you can use INDIRECT:

=INDIRECT("A" & B1)

This takes a cell reference as a string (in this case, the concatenation of A and the value of B1 - 5), and returns the value at that cell.

6
votes

This should do the trick! :)

B1 =ROW(A5)

http://www.techonthenet.com/excel/formulas/row.php

4
votes

An alternative is to use OFFSET:

Assuming the column value is stored in B1, you can use the following

C1 = OFFSET(A1, 0, B1 - 1)

This works by:

a) taking a base cell (A1)
b) adding 0 to the row (keeping it as A)
c) adding (A5 - 1) to the column

You can also use another value instead of 0 if you want to change the row value too.