How to Link Worksheets and Create Calculation Formulas in Excel
Question details
The user needs to learn how to pull data from one worksheet to another, perform mathematical calculations on that data, and set up variance flags based on the results.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Organizing complex data across multiple worksheets, pulling matching records, and analyzing numerical differences with conditional flags.
- Observed behavior
- Needs a reliable method to dynamically link tables, execute mathematical operations across sheets, and conditionally flag data variances.
Ensure that both your source and destination worksheets are located within the same workbook to prevent broken links. Identify a unique common identifier (like an Employee ID or Product Code) that exists in both sheets to match the data accurately.
Use VLOOKUP and IF Formulas to Pull Data and Flag Variances
Use lookup functions to retrieve data from a source sheet, perform basic subtraction for the variance, and use an IF statement to create a dynamic variance flag.
Linking worksheets requires referencing cells or ranges from another sheet. Using VLOOKUP (or XLOOKUP) allows you to match records dynamically, which is much safer than direct cell references if your data order changes.
Once the data is pulled into your destination sheet, you can use standard arithmetic operators to calculate the variance and nest an IF function to display text warnings.
In your destination worksheet, select the cell where you want the data to appear. Type `=VLOOKUP(A2, SourceSheet!A:D, 2, FALSE)`, replacing 'A2' with your lookup value, 'SourceSheet!A:D' with your source data range, and '2' with the column index containing the data.
In the adjacent column, create a formula to calculate the difference. For example, type `=C2-B2` to subtract the target value from the actual value.
In the next column, use an IF function to flag significant variances. Type `=IF(D2>100, "High Variance", "OK")` to automatically label any variance greater than 100.

Easily Link Sheets and Calculate Data with WPS Spreadsheet
WPS Spreadsheet provides robust functions like VLOOKUP, XLOOKUP, and IF statements to help you seamlessly link data across multiple worksheets and automate complex variance calculations.
- 1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your datasets.
- 2. Initiate the formula: Click on the destination cell where you want to pull data or calculate a variance and type `=` to begin your formula.
- 3. Select cross-sheet references: While typing the formula, simply click on the tab of your source sheet and select the required cells. WPS will automatically insert the correct sheet reference (e.g., `Sheet1!A1`).
- 4. Apply logical functions: Combine your references with the `IF` function from the 'Formulas' tab to set up automated variance flags for your data.

Frequently Asked Questions
How do I do a simple cell reference to another worksheet?
To reference a specific cell in another sheet, type `=` in your destination cell, click the tab of the source sheet at the bottom of the screen, and click the cell you want to link. Press Enter, and your formula will look like `=SheetName!A1`.
Why is my VLOOKUP returning an #N/A error when pulling data across sheets?
The #N/A error typically occurs when the lookup value cannot be found in the first column of your source table. Ensure that the values match exactly, check for hidden leading/trailing spaces, and verify that your lookup range is locked with absolute references (e.g., $A$1:$D$100).
Can I link data between two completely separate Excel files?
Yes. With both files open, start your formula and click the cell in the other workbook. The formula will include the file name in square brackets. However, it is highly recommended to keep linked sheets within the same workbook to prevent broken links if files are moved or renamed.
How do I highlight my variance flags with colors?
Select the cells containing your IF formula variance flags, navigate to the Home tab, and click 'Conditional Formatting'. Choose 'Highlight Cells Rules' > 'Text that Contains', and set it to highlight specific flag text (like 'High Variance') in red.




