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:
- Relative path: The path to the external document described relative to the base directory of the source document. Example: ../Subfolder/External document.xlsx.
- Absolute 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.
- The 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 Windows
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.
| Relative path to external document | ./external.xlsx |
| Absolute path to external document | C:/Users/Username/Downloads/external.xlsx |
| The absolute path to the external document using the file scheme (URI scheme) | file:///C:/Users/Username/Downloads/external.xlsx |
| Formula with relative reference | ='[external.xlsx]Sheet 1'!A1+A1 |
| Function with absolute reference | =SUM('file:///C:/Users/Username/Downloads/[external.xlsx] Sheet1'!A1:A10) |
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/.
| Relative path to external document | ./Subfolder/external.xlsx |
| Absolute path to external document | C:/Users/Username/Downloads/Subfolder/external.xlsx |
| The absolute path to the external document using the file scheme (URI scheme) | file:///C:/Users/Username/Downloads/Subfolder/external.xlsx |
| Formula with relative reference | ='Subfolder/[external.xlsx]Sheet1'!A1+A1 |
| Function with absolute reference | =SUM('file:///C:/Users/Username/Downloads/Subfolder/[external.xlsx] Sheet1'!A1:A10) |
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/.
| Relative path to external document | D:/Documents/SomeFolder/external.xlsx |
| Absolute path to external document | D:/Documents/SomeFolder/external.xlsx |
| The absolute path to the external document using the file scheme (URI scheme) | file:///D:/Documents/SomeFolder/external.xlsx |
| Formula with relative reference | ='D:/Documents/SomeFolder/[external.xlsx ]Sheet1'!A1+A1 |
| Function with absolute reference | =SUM('file:///D:/Documents/SomeFolder/[external.xlsx] Sheet1'!A1:A10) |
Examples for Linux
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.
| Relative path to external document | ../external.xlsx |
| Absolute path to external document | /Users/Username/Downloads/external.xlsx |
| The absolute path to the external document using the file scheme (URI scheme) | file:///Users/Username/Downloads/external.xlsx |
| Formula with relative reference | ='[external.xlsx]Sheet 1'!A1+A1 |
| Function with absolute reference | =SUM('file:///Users/Username/Downloads/[external.xlsx] Sheet1'!A1:A10) |
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/.
| Relative path to external document | ../Subfolder/external.xlsx |
| Absolute path to external document | /Users/Username/Downloads/Subfolder/external.xlsx |
| The absolute path to the external document using the file scheme (URI scheme) | file:///Users/Username/Downloads/Subfolder/external.xlsx |
| Formula with relative reference | ='Subfolder/[external.xlsx]Sheet1'!A1+A1 |
| Function with absolute reference | =SUM('file:////Users/Username/Downloads/Subfolder/[external.xlsx] Sheet1'!A1:A10) |
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/.
| Relative path to external document | /Volumes/USB_flash/SomeFolder/external.xlsx |
| Absolute path to external document | /Volumes/USB_flash/SomeFolder/external.xlsx |
| The absolute path to the external document using the file scheme (URI scheme) | file:///Volumes/USB_flash/SomeFolder/external.xlsx |
| Formula with relative reference | ='../Volumes/USB_flash/SomeFolder/[external.xlsx ]Sheet1'!A1+A1 |
| Function with absolute reference | ='file:///Volumes/USB_flash/SomeFolder/[external.xlsx] Sheet1'!A1:A10) |