I have a spreadsheet created in Excel and now using libre office calc ( I have saved the file in the ODS extension) and there is a cell in sheet 2 that reference a cell in sheet 1. I have tried to simply copy the cell from sheet 1 to the cell in sheet 2 and this doesnt work. I have tried to type in directly and no success. The result is the cell shows #NAME? Is there a way to reference the cell in sheet 1 and use it in sheet 2?
Thanks in advance
KORA
I have found a work around. I have renamed the sheet to Sheet1 and changed the formula to reflect that and it now works. previous formula is shown below
=SUM(’[1]print sheet i’!R10:R49)
After I renamed the sheet the formula now looks like below and working correctly.
=SUM($Sheet1.R10:R49)
Not sure why this worked I suspect when saved into ods format there was something lost in the process
Hello,
two reasons
1) Use of invalid characters
your previous sheet name used invalid characters (i.e. [ and ]). See LibreOffice Help - Renaming Sheets which states:
Sheet names can contain almost any character. Some naming restrictions apply, the following characters are not allowed in sheet names:
colon :
back slash \
forward slash /
question mark ?
asterisk *
left square bracket [
right square bracket ]
single quote ' as the first or last character of the name
2) Invalid separator
Your original formula used an exclamation mark ! separating the sheet name from the cell range, which is the separator - afaik - in Excel while LibreOffice uses a . (dot) as separator.
That’s why renaming the sheet results in a working formula and your solution is not a workaround.
Thanks for the input I will keep it in mind for further work int he sheet.
KORA
It’s really interesting how that sheet name happened … I just tried to create one in Excel, and failed - is it actually possible somehow?
You may be using an old version of LibreOffice (you didn’t say which) that had a bug when importing .xlsx files with sheet references.