Ask Your Question

reference cell formula in sheet 1 and use in sheet 2

asked 2019-12-01 21:52:19 +0100

KORA gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-12-01 22:05:46 +0100

KORA gravatar image

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

edit flag offensive delete link more



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.

Opaque gravatar imageOpaque ( 2019-12-01 22:31:39 +0100 )edit

Thanks for the input I will keep it in mind for further work int he sheet.


KORA gravatar imageKORA ( 2019-12-01 22:46:42 +0100 )edit

It's really interesting how that sheet name happened ... I just tried to create one in Excel, and failed - is it actually possible somehow?

Mike Kaganski gravatar imageMike Kaganski ( 2019-12-01 23:21:41 +0100 )edit

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.

erAck gravatar imageerAck ( 2019-12-02 15:42:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-12-01 21:52:19 +0100

Seen: 13 times

Last updated: Dec 01