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 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.
Relative path to external document |
../external.xlsx |
|---|---|
Absolute path to external document |
C:/Users/Username/Downloads/external.xlsx |
Absolute path to an 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 |
Absolute path to an 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 |
Absolute path to an 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 OS and macOS
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 |
Absolute path to an 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 |
Absolute path to an 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 |
Absolute path to an 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) |