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

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.


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

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

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…

=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…

1 Like