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
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"))