Reference to data in another document
In the formulas and functions of the current document, you can refer to data from one or more external documents.
The following restrictions apply in this version of the application:
•The current and external documents must be saved in .xlsx, .ods or .xods format.
•The current and external documents must be local, that is, they must be located directly on your computer.
•The creation of references to cells and cell ranges of an external document is supported. The use of structured references, as well as references to cell names and ranges, is not allowed.
•The reference that is entered manually into the cell with the name must be absolute. When creating a data source for a named range, you need to enter an absolute reference for correct functioning.
You can create a reference to data from an external document in one of the following ways:
•Open an external document and select the desired cell or range of cells with a mouse click.
•Enter the reference manually without opening an external document.
Create a reference with a mouse click
To create a reference to data from an external document in a formula or function of the current document by clicking the mouse, follow these steps: 1.Open a document that will contain a formula or function with a reference, and an external document to which data you want to create a reference. If the external document was opened earlier, make sure that all the changes you made are saved in it. 2.In the current document, select the cell where you want to enter a formula or function. 3.In the Formula bar or directly in the cell, enter the = sign and start typing the formula or function. 4.Navigate to the external document and select the sheet whose cell or range of cells you want to refer to. 5.Select the desired cell or range of cells on the sheet. The Formula bar of the external and current document displays the entered formula or function from the current document. If necessary, the formula/function can be edited both in the current and in an external document. 6.Go to the current document, finish entering the function or formula and click |
in the Formula bar or press Enter the reference manually
To manually create a reference to data from an external document in a formula or function of the current document, enter a string of the following type: 'path[name.extension]Sheet'!Cell where: •path: the path to the external document, which can be specified as: oRelative path: The path to the external document described relative to the base directory of the source document. Example: ../Subfolder/External document.xlsx. oAbsolute path: The path to the document described from the root of the logical disk (the root of the installed operating system). Example: C:/Users/Username/Downloads/Subfolder/External document.xlsx. oThe absolute path to the external document using the file scheme (URI scheme). Example: file:///C:/Users/Username/Downloads/Subfolder/External document.xlsx. •name.extension: The name and extension of the external file. For example: External document.xlsx. •Sheet: The name of the sheet in the external document. Example: Sheet1. •Cell: The address of a cell or range of cells in an external document. For example: A1 or A1:A10. Examples for Microsoft Windows OS In this example, the document document.xlsx located in the base directory C:/Users/Username/Downloads/ refers to data from an external document external.xlsx located in the same directory.
In this example, the document document.xlsx located in the base directory C:/Users/Username/Downloads/ refers to data from an external document external.xlsx located in a nested directory C:/Users/Username/Downloads/Subfolder/.
In this example, the document document.xlsx located in the base directory C:/Users/Username/Downloads/ refers to data from an external document external.xlsx located in another directory – D:/Documents/SomeFolder/.
Examples for Linux OS In this example, the document document.xlsx located in the base directory /Users/Username/Downloads/ refers to data from an external document external.xlsx located in the same directory.
In this example, the document document.xlsx located in the base directory /Users/Username/Downloads/, refers to data from an external document external.xlsx located in the nested directory /Users/Username/Downloads/Subfolder/.
In this example, the document document.xlsx located in the base directory /Users/Username/Downloads/ refers to data from an external document external.xlsx located in another directory, /Volumes/USB_flash/SomeFolder/.
|
Update data
Since the data in the external document may change, it is recommended to periodically update the reference to this data in the current document. To update the references after opening the current document, click the Refresh Data button in the notification line "Linked documents might have changed. Refresh this document to get the latest data." This line is displayed under the Toolbar. The application will recalculate formulas and functions taking into account new values from an external document. If the document is not available, the notification line "Linked documents are unavailable. The spreadsheet uses the last saved data." Click the Close button in this line. To update the data when working with a document, do one of the following: •In the Command menu, select Data > Refresh Data. •On the Toolbar, in the Data section, click •Press Ctrl +Shift+F5. Data on external references are updated automatically when a new document reference is created or when an existing one is edited. In this case, the data is updated only for those documents that are specified in the created or edited formula. #REF! error is displayed in cells in the following cases:
•When creating a reference, the external document specified in the reference is unavailable (or the specified sheet does not exist in it), and there are no previously saved values for it. •When uploading a document, there are no saved values for the external document specified in the reference (the data was not received when creating the reference). |
