Referencing Other Sheets
In a sheet cell you can show a reference to a cell in another sheet.
In the same way, a reference can also be made to a cell from another document provided that this document has already been saved as a file.
To Reference a Cell in the Same Document
-
Open a new, empty spreadsheet. By default, it has only a single sheet named Sheet1. Add a second sheet clicking on + button to the left of the sheet tab in the bottom (it will be named Sheet2 by default).
-
By way of example, enter the following formula in cell A1 of Sheet1:
=Sheet2.A1
-
Click the Sheet 2 tab at the bottom of the spreadsheet. Set the cursor in cell A1 there and enter text or a number.
-
If you switch back to Sheet1, you will see the same content in cell A1 there. If the contents of Sheet2.A1 change, then the contents of Sheet1.A1 also change.
When referencing a sheet with name containing spaces, use single quotes around the name: ='Sheet with spaces in name'.A1
The example uses Calc formula syntax. It is also possible to use Excel A1 or R1C1 formula syntax; this is configured on Formula options page.
To Reference a Cell in Another Document
-
Choose File - Open, to load an existing spreadsheet document.
-
Choose File - New, to open a new spreadsheet document. Set the cursor in the cell where you want to insert the external data and enter an equals sign to indicate that you want to begin a formula.
-
Now switch to the document you have just loaded. Click the cell with the data that you want to insert in the new document.
-
Switch back to the new spreadsheet. In the input line you will now see how Collabora Office Calc has added the reference to the formula for you.
The reference to a cell of another document contains the name of the other document in single inverted commas, then a hash #, then the name of the sheet of the other document, followed by a point and the name of the cell.
-
Confirm the formula by clicking the green check mark.
-
If you drag the box in the lower right corner of the active cell to select a range of cells, Collabora Office automatically inserts the corresponding references in the adjacent cells. As a result, the sheet name is preceded with a "$" sign to designate it as an absolute reference.
If you examine the name of the other document in this formula, you will notice that it is written as a URL. This means that you can also enter a URL from the Internet.