Pass row number as variable in excel sheet

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?


Solution 1:

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.

Solution 2:

This should do the trick! :)

B1 =ROW(A5)

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

Solution 3:

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.