How to Link Two Excel Workbooks with an IF Formula
Question details
The user needs to automatically copy data from a specific cell or range in one Excel workbook to another workbook based on a specific condition, such as a dropdown value.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating dynamic links across different Excel workbooks to consolidate data or filter ranges conditionally using an IF statement.
- Observed behavior
- Users attempting to write an IF formula referencing another workbook often encounter errors or fail to pull data due to incorrect syntax, missing argument separators, or inaccurate file and sheet names.
Ensure both the source and destination workbooks are saved to your computer and currently open. This makes it easier to click and select ranges without manually typing complex file paths.
Use an External-Reference IF Formula via Point-and-Click
The most reliable way to create a cross-workbook IF formula is by letting Excel build the syntax automatically as you click the source cells.
By pointing and clicking, the software automatically adds the correct brackets around the workbook name and quotation marks where necessary, preventing common typing errors.
Launch Excel and open both the destination workbook (where you want the data to appear) and the source workbook (which contains the raw data).
In the destination workbook, click the cell where you want the result to appear and type `=IF(`.
Switch to the source workbook window, click the cell containing your status or dropdown (e.g., K6), and then type your condition in the formula bar, such as `="Send",`.
Still in the source workbook, click the cell or highlight the range you want to copy if the condition is met (e.g., B6:H6). Then type `,"")` to tell Excel to leave the cell blank if the condition is not met, and press Enter.

Manually Type the External Reference Formula
If you prefer typing or are referencing a workbook that is currently closed, you can manually type out the exact file path and reference syntax.
Easily Link Multiple Spreadsheets with WPS Office
WPS Spreadsheet provides a seamless and intuitive environment for linking data across multiple workbooks. Using standard IF formulas with external references works flawlessly and quickly.
- 1. Open Workbooks in WPS Spreadsheet: Launch WPS Office and open both your source and destination workbooks. They will conveniently open in the same window as separate tabs.
- 2. Begin Your Formula: In the destination tab, select your target cell and type `=IF(` to begin the logical test.
- 3. Select Source Criteria: Click over to your source tab, select the cell containing your criteria, and complete the logical test by typing `="Send",`.
- 4. Finalize the Output Range: Highlight the range you want to copy from the source tab, type `,"")` to set a blank fallback, and hit Enter.

Frequently Asked Questions
Why is my external reference formula returning a #REF! error?
A #REF! error typically occurs if the linked source workbook has been moved to a different folder, renamed, or deleted. You can fix this by navigating to the Data tab, clicking 'Edit Links', and updating the source path to point to the correct file.
Can I link to a closed workbook using an IF formula?
Yes, you can pull data conditionally from a closed workbook. However, you must ensure the external reference includes the entire file path on your computer, enclosed in single quotation marks. For example: ='C:\Documents\[Data.xlsx]Sheet1'!A1.
Why does my IF formula say it is missing a comma or separator?
This is usually caused by your operating system's regional settings. While most regions use a comma (,) to separate arguments in an IF formula, regions that use commas as decimal separators will require you to use a semicolon (;) instead. Try replacing the commas in your formula with semicolons.




