Search code examples
libreoffice-calc

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.

Solution

  • 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