1

Using LibreOffice Calc, Version: 6.4.4.2

I am having trouble using a text cell's content as a sheet reference. I have used previous solutions using INDIRECT but something is still not right.

The manual insertion of the sheet name in cell B6 works:

=$'Jul-10-2020'.E3

That successfully returns the contents of cell E3 in the sheet named "Jul-10-2020".

But I need to automate it a bit and use a cell that already has the sheet name in it. For example, cell A6 contains:

"Jul-10-2020"

A6 is formatted as text, not date.

I think INDIRECT is the right function to use, but I am missing something:

=$'(Indirect("A6"))'.E3

This returns the error "#REF!"

When I do a simple test of INDIRECT, it works:

=Indirect("A6")

Returns "Jul-10-2020" which is the text contents of A6.

What am I missing?

Monty

Monty
  • 13
  • 2

1 Answers1

1

You want to dynamically retrieve the value of the cell E3 on sheet Jul-10-2020, which would be hard-coded as:

=$'Jul-10-2020'.E3

(or, in a more general format: $'Sheetname between single quotes because it contains special characters'.A1; the single quotes for the sheet name could be avoided if the sheet name doesn't use special characters)

In your example, the name of the sheet is in cell A6 of the actual sheet.

One possibility could be to nest two INDIRECT() calls, like this:

=INDIRECT("$"&INDIRECT("A6")&".E3")

You could also "build up" the correct string for the INDIRECT() call with CONCAT():

=INDIRECT(CONCAT("$",A6,".e3"))

m0r0dan
  • 149
  • 8
  • Thanks! But the function wizard doesn't like (apparently) the first INDIRECT in the nested solution; still get the "#REF!" error for the first INDIRECT. Green check marks on all else; the second INDIRECT results are "Jul-10-2020", which are the contents of A6. In the CONCAT solution, the INDIRECT shows the same error, but green check marks everywhere else. – Monty Jul 20 '20 at 18:39
  • Mmmh, maybe one little thing with big consequences: the value of the cell A6 should be `'Jul-10-2020'` (single quote), not `"Jul-10-2020"`. Could this be the problem? Also, check if, in your locale, you need `,` or `;` as separation sign for the `CONCAT()` formula? (But if you use the wizard this should already be correct) – m0r0dan Jul 20 '20 at 18:46
  • found the issue! The problem was I had missed a " ' " character at the beginning of the date field, A6. Once I removed that and got a proper text date, your solution worked! – Monty Jul 20 '20 at 18:54
  • Glad it has helped you! – m0r0dan Jul 20 '20 at 19:14