How to refer to a cell when the address is in a variable

Question

For example, in an expression, instead of writing the address A1, how can I write something like: A(B1) with B1 = 1.

Answer 1

I think another way of explaining what INDIRECT does is this way:

It turns text into a range, if that range is valid.

E.g. If you have text A1, it'll reference to A1. If you have text C2:C100, you'll get this as range.

Now, one of the most common ways in excel to generate text in the form of ranges is to concatenate. So that if you concatenate A and 1 (CONCATENATE("A","1")), you get A1.

And you can use a reference in this concatentate. Let's say that cell B1 contains 1.

=CONCATENATE("A",B1)

gives the text A1.

Hence, to get the cell A1, you would be able to use:

=INDIRECT(CONCATENATE("A",B1))

Except that the CONCATENATE() function now is a bit long, but don't fret! You can use &:

=INDIRECT("A"&B1)

Works just as well.

If you have something more complex like you have C in A1 and 32 in B1, to refer to cell C32, you can do:

=INDIRECT(A1&B1)

Which gives =INDIRECT("C"&"32"), =INDIRECT("C32") and finally =C32

Answer 2

`INDIRECT' is the function you need to use.

From the documentation:

Syntax:

INDIRECT(Ref; A1)

Ref represents a reference to a cell or an area (in text form) for which to return the contents.

A1 (optional) - if set to 0, the R1C1 notation is used. If this parameter is absent or set to another value than 0, the A1 notation is used.

Example

=INDIRECT(A1) equals 100 if A1 contains C108 as a reference and cell C108 contains a value of 100.

=SUM(INDIRECT("a1:" & ADDRESS(1;3))) totals the cells in the area of A1 up to the cell with the address defined by row 1 and column 3. This means that area A1:C1 is totaled.

Answer 3

=OFFSET(B1, 0, 1)

OFFSET(reference, row offset, column offset, area height defaults to 1, area width defaults to 1)

Example 1. We have multiplication table and need to find the answer for some stupid reason with OFFSET.

   A   B  C  D  E  F
1      1  2  3  4  5
     ---------------
2  1 | 1  2  3  4  5
3  2 | 2  4  6  8 10
4  3 | 3  6  9 12 15
5  4 | 4  8 12 16 20
6  5 | 5 10 15 20 25

Let's say we need 2x4 =OFFSET(A1, 2, 4) or in this case switching the numbers works equally well =OFFSET(A1, 4, 2)

Example 2. Let's calculate sum of all the numbers in the previous multiplication table which are for 2 or larger. =SUM(OFFSET(A1, 2, 2, 4, 4))