When you are working with multiple workbooks or worksheets, it is often needed to refer back or retrieve data from different workbooks into the current workbook. This helps to keep your work modular and less complex. In LibreOffice Calc, you can achieve it using an external reference.
External reference, as its name says, a reference to an external file (workbook) or a cell inside that workbook. And the reference can be of any form with a fully qualified path with folders or servers.
There are two types of external reference which you can do it in Calc.
- Calc reference to another sheet.
- Calc reference to another workbook.
Table of Contents
How to reference another sheet in Calc
To reference any cell or range of cells in another worksheet, put dollar “$” followed by sheet name then a dot (.), then the cell address.
$sheet_name.cell_address
For example, to refer to cell C3 in Sheet2, you should use
=$Sheet2.C3
To refer a range of cells in your formula, use the below format.
=$sheet_name.firstcell:lastcell
For example, to refer cells from C3 to E4 in Sheet2, use below.
=$Sheet2.C3:E4
Note
If your sheet name contains spaces or special characters, enclose the sheet name in single quotes (‘). Like below.
=$'Project Plan'.A1:A3
How to reference to another workbook in Calc
To create an external reference to a workbook, use the workbook name followed by “#” and a dollar sign “$”, then sheet name followed by a dot “.” and cell or range address. So, according to this, you can use the below reference format to create an external reference to an open workbook:
workbook_name#$sheet_name.firstcell:lastcell
MainProject.ods#$'Project Plan'.A1:A3
For example, if you want to sum the above range A1:A3, you can use the below formula:
=SUM(MainProject.ods#$'Project Plan'.A1:A3)
Examples and Usage Notes
- Always use the full path of the *.ods Calc files in your reference formula.
- For Linux based systems, use below formula as shown in the example:
='file:///home/arindam/price.ods'#$'fruit price'.B2
- For Windows, use below formula as shown in the example:
='file:///D:/price.ods'#$'fruit price'.B2
- If your file name or sheet name having special character such as space, you should enclose the formula in the single quote (‘) as below:
- When you refer to an external workbook, Calc always uses absolute reference. So, remember to change the dollar ($) sign to make relative or mixed reference as per your need.
Drop a comment below if this article helped you or if you have any questions.
Can you please mention if it is possible to refer to a defined name in an external workbook and give an example if it is possible?
Can you please also give an example of an external reference using INDIRECT?
Thanks.
But these links are not “Live”. I have to hit F9 to Recalculate every time the source value changes.
I use one spreadsheet to calculate a function from the argument that I supply (like a function or a subroutine in a programming language). I supply (link) the argument from my other workbooks to this subroutine workbook, and then link the resulting value back from the subroutine to the initial book. In Excel, if the argument changes in the main book, it changes automatically in the subroutine, the new value is calculated and returned back to the main book, all automatically. In LibreOffice Calc, I have to switch to the subroutine, hit F9 to update the link, then switch back to the main book, hit F9 there to update the function value. Extremely inefficient and tedious if I have to do it many times.
Is there a setting to make it all happen automatically?
It should work automatically, looks like a bug here. https://bugs.documentfoundation.org/show_bug.cgi?id=137248
Which version of LO and operating system you are using?
If I have empty cells within the range, the mirrored cell ends up with a “0” in it. Is there a way to prevent that?
I keep getting ERR:509 when trying to reference data from another file. The formula is correct based on the info above so I don’t know how to correct this.
Can you share screenshot of both the cells
Hi Akismet, I'm using LO Calc 24.2.5.2 (X86_64) on Linux Mint 22 Wilma I have a spreadsheet using workbook_name#$sheet_name.firstcell:lastcell When I 'Move or Copy' the sheet the copy changes to workbook_name#$sheet_name.firstcell:workbook_name#$sheet_name+1.lastcell It would be OK (but ugly) if it did not increment the end_range sheet name. I have tried moving the source sheet to the beginning of the workbook and copying it before itself (as suggested elsewhere) but it makes no difference. The following is an actual example =IF(AS5<>"",VLOOKUP(AT25,'file:///home/john/live_files/spreadsheets/food_diary.ods'#$metrics.$A$33:$R$399,18,0),"") became =IF(AS5<>"",VLOOKUP(AT25,'file:///home/john/live_files/spreadsheets/food_diary.ods'#$metrics.$A$33:'file:///home/john/live_files/spreadsheets/food_diary.ods'#$2024_09.$R$399,18,0),"") The sequence of sheets in the food_diary.ods workbook is {9 sheets} metrics 2024_09 {14 more sheets}. Any ideas? Regards, JohnWhoops! I meant ‘Hi Arindam’. Sorry. John
Hi Arindam,
Problem solved.
The end_range doesn’t get incremented if I select the ‘- move to end position -‘ option when I copy it.
It’s a lot easier to move the new sheet to where I want it rather than having to change 30+ incremented end_range sheet names.in the new sheet.
I don’t know why I didn’t think of trying that anytime over the years – or even trying it before I posted earlier – but I’ll put ‘raise a bug report’ on my ‘to do’ list.
Regards,
John