Reference cell formula in sheet 1 and use in sheet 2

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.