Search code examples
excelcelllibreofficelibreoffice-calc

How do I dynamically change input cell in a formula based on the value in another cell in a spreadsheet?

It is possible to dynamically change the input cell address in a formula based on the content in another cell?

Let's say I have a spreadsheet (excel or libreoffice) with these cell values:

A1: 10
A5:  9
C1:  5

Instead of hardcoding =A1-A5 , I would like to do something like this: =A1-A(C1), that would be evaluated at run time to use cell A5 for the second input.

Solution

  • Use INDIRECT to take the value in C1 as a pointer to the row that you want in column A:-

    =A1-INDIRECT("A"&C1)

    (tested in Open Office and Excel 2010)