How to refer to a cell when the address is in a variable
For example, in an expression, instead of writing the address A1, how can I write something like: A(B1) with B1 = 1.
Answer
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