Referencing a variable cell in a formula

[erikh92]

Is there a way I can set up a formula in terms of a variable cell? For example, suppose A1 = 5 and that I want to reference a cell with column B and row = A1 = 5. Is there a straightforward way of doing this? Anything with syntax as straightforward as something like "B[A1]"?

Can this be similarly done for columns? For example, if A1 = D, then how about something like "[A1]2" to reference "D2"? It would be even better if the cell could be numerical (with A=1, B=2, C=3, etc).

This may all seem rather unmotivated, but having recently gotten into Excel from more of a programming background, this is something that has repeatedly entered my mind.

[leelnich]

Hi erikh92- You want to look into the INDEX and OFFSET functions.

INDEX(array, row_num, [column_num]) - returns an element in a 1- or 2- dimensional array ( or range)

OFFSET(reference, rows, cols, [height], [width]) - returns a cell or cells offset by specified # of rows and columns