4

In LibreOffice Calc I want to reference a sheet and a cell depending on the values in other cells. For example with

=INDIRECT(C$3 & ".D24")

I can reference the sheet, depending on the value of C3 and there the cell D24. That works quite well, but the cell is hardwritten here. I would like do have a reference cell for D and maybe for 24 as well. But the 24 could also be dynamically changed like calc usually does when the $ lock sign isn't used.

I tried:

=INDIRECT(C$3 & CONCATENATE(C1; ROW()))

which is not working unfortunately... C1 value is D in this example

Asara
  • 2,791
  • 3
  • 26
  • 55

2 Answers2

1

ok cool I got it by using

=INDIRECT(C$3 & CONCATENATE(".";$C$1;ROW()))
  • C3 value is the name of the other sheet
  • C1 value is D for the column on the other sheet
Asara
  • 2,791
  • 3
  • 26
  • 55
0

For 24 as well (say in C2), maybe:

=INDIRECT(C$3&"."&C$1&C$2+ROW()-3)

if to copy down, assuming result is to be in Row3 (or adjust offset).

pnuts
  • 58,317
  • 11
  • 87
  • 139