Cross-spreadsheet references rely on the IMPORTRANGE function to import cell data from a target range in a specified spreadsheet and automatically sync updates from the source spreadsheet. With cross-spreadsheet references, you can connect data across multiple spreadsheets in a single step, making data processing more efficient.
- Use case overview
- How to create a reference
- View or edit existing references
- Cancel a reference
- Troubleshooting cross-spreadsheet reference failures
- FAQ
1. Use case overview
Ding Sanduo is the national store manager of DingTalk Coffee, and needs to consolidate the sales performance of stores nationwide in real time every day. Ding Sanduo wants a master spreadsheet that automatically references the sales data of stores in different regions, instead of copying the data manually. This maintains efficiency and ensures that any subsequent updates sync automatically to all spreadsheets that reference the data.
2. How to create a reference
Method 1: From the feature menu Select a cell, then click Menu > Data > Cross-spreadsheet reference > Create reference in the toolbar. In the dialog, select or search for the target spreadsheet file, choose the data range on the right, and click Reference data to complete the action. Method 2: Use the IMPORTRANGE function Select a cell and enter “=IMPORTRANGE” to open the function panel. Click Select spreadsheet, select or search for the target spreadsheet file in the dialog, choose the data range on the right, and click Reference data to complete the action. If you are familiar with functions, enter the formula directly: =IMPORTRANGE(spreadsheet link, range) For example:=IMPORTRANGE("https://docs.dingtalk.io/...","'Project Management'!G21:G22")
Method 3: From the function menu
Select a cell, then click Menu > Insert > Formula > All functions in the toolbar. Find the IMPORTRANGE function under Lookup, click Insert > Select spreadsheet, choose the target spreadsheet file in the dialog, and click Reference data to complete the reference.
❤️ Tips
After confirming the reference, an authorization prompt appears in a small window. Click Allow, otherwise the reference cannot take effect.
3. View or edit existing references
Users with Can edit or higher permissions on the current spreadsheet can view cross-spreadsheet references in the current worksheet. How to view:- View all references: From the main Menu, click Data > Cross-spreadsheet reference > View all references.
- View references in a selection: Right-click within the selection to open the context menu, scroll to the bottom, and select Cross-spreadsheet reference.
(Note: Users with Can edit or higher permissions on the current spreadsheet can use the Open entry.)Method 1: Open the view panel and click the referenced spreadsheet on the spreadsheet card on the right to navigate to it. Method 2: Right-click the referenced selection, scroll to the bottom of the menu, and select Cross-spreadsheet reference > Open source spreadsheet.
4. Cancel a reference
Note: Only users with Can edit or higher permissions on the current spreadsheet can cancel a reference.Method 1: Open the view panel and click the cancel button on the spreadsheet card on the right. Method 2: Right-click the referenced selection, scroll to the bottom of the menu, and select Cross-spreadsheet reference > Cancel reference.
5. Troubleshooting cross-spreadsheet reference failures
- Glossary
Node: A “cross-spreadsheet reference node” refers to a document or data unit that is being referenced. In a cross-spreadsheet reference, each individual document, spreadsheet, or table can be regarded as a “node”. These nodes are connected through reference relationships, forming a network or graph structure.
- Categories and causes of cross-spreadsheet reference failures
- Node count limit
-
Node count limit explanation: The node count refers to the total number of documents directly or indirectly connected through references in the network. The current limit is 10 nodes. If the total exceeds 10, authorization fails with a message indicating the limit has been reached. For example:
- A references B and C; B references D, E, and F. The total number of related documents is 6.
- When the total reaches 10, none of the 10 documents can reference an 11th document, nor can the 11th reference any of them.
- If the combined total of two reference groups exceeds 10, the two groups cannot be linked.
- Node depth limit
-
Node depth limit: The node depth refers to the depth of references along a single path—document A is referenced by document B, which is referenced by document C, and so on. The current depth limit is 3 levels. For example:
- A references B, B references C, and C references D. There are 3 levels of references among these four documents. D cannot reference a fifth document, nor can a fifth document reference A.
- Circular references
-
Circular reference: If document A references document B, and document B directly or indirectly references document A, forming a closed loop, the reference is not supported. This avoids infinite loops or logical confusion in data processing. For example:
- A references B, and B references C. C cannot reference A or B. A document cannot reference itself either.
- Notes for using cross-spreadsheet references
- The number of related referenced documents cannot exceed 10.
- Referenced documents do not support online editing.
- Referencing too much content or formulas that are too complex may cause a timeout and lead to failure.
6. FAQ
- Cross-spreadsheet references do not currently support local spreadsheets.
- Spreadsheets containing “Lock - View prohibited” rules cannot be referenced.
- The referenced range cannot contain other data. Cross-spreadsheet references rely on “array formulas”. When the array expands, the target area must be empty.
Back to Spreadsheet