External Reference

LibreOffice Calc Reference to Another External Sheet or Workbook

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.

External Reference
External Reference

There are two types of external reference which you can do it in Calc.

  1. Calc reference to another sheet.
  2. Calc reference to another workbook.

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.


For example, to refer to cell C3 in Sheet2, you should use


To refer a range of cells in your formula, use the below format.


For example, to refer cells from C3 to E4 in Sheet2, use below.



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:

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.
SEE ALSO:  Repeat Rows Headers or Columns in Every Print Pages in Calc

Drop a comment below if this article helped you or if you have any questions.

Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.

Join our Telegram channel and stay informed on the move.



IT professional by profession and founder of libreofficehelp.com. Loving Linux and other technologies since 2002. I believe in open source and its philosophy. Follow me on Twitter or email me.

One thought to “LibreOffice Calc Reference to Another External Sheet or Workbook”

  1. 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?


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.