How to enter double-quotes and a cell reference in a calc formula

[StonehouseTraveller1]

I have been given an Excel spreadsheet with a field with the following format (text) ="123456"

I am trying to replicate this from a sheet with many rows (the value is in ACE Inventory List.A2…) when I enter the formula

="="&"'""&$'ACE Inventory List'.A2&""'"

calc returns ="&$'ACE Inventory List'.A2&"

so it is successfully interpreting """" as ", but it is not executing the cell reference.

If I enter =$'ACE Inventory List'.A2 in a cell I get the contents of the other cell.

I know it’s wierd formatting, and when I asked was told it’s their format.

Any suggestions for acheiving this in Libreoffice would be very helpful.

Paul

Tried moving the values into the same sheet (cell k3) and the formula ="="&""""&K3&"""" works What can I do to have it work across sheets?

[erAck]

Edited question to format code as code… see This is the guide - How to use the Ask site? - #6 by erAck .

[Wanderer]

Never experienced this, because I avoid spaces in Sheet-Names, so my first “easy” workaround would be to rename the sheet, using underscore instead of space.
.
INDIRECT Function should then have no problem with your address…

[Wanderer]

=INDIRECT("$'ACE Inventory List'.A2")

works for me as well as

=INDIRECT("$'ACE Inventory List'"&"."&"A2")

and I can use also directly

=$'ACE Inventory List'.A2

.
Then I checked

="="&""""&$'ACE Inventory List'.A2&""""

and I got ="4711" as I put 4711 in that cell.
In your first example you used a mix of single and double quote "'"", wich failed.
Your working example with K3 has only double quotes """".
.
Attention: The result is no formula, but a string, wich starts with =
You can not use this to calculate…