Formula Errors

How to Use an Excel Formula to Exclude Loading for One Customer

Maira Mehtab Sep 27, 2026 872 views

Question details

The user needs to create an Excel formula within a structured table that applies a different calculation (excluding a loading cost) when a customer code on a master worksheet matches a specific value.

Product: Excel
Device & OS: not provided
Scenario: Calculating conditional pricing within an Excel Table based on a customer code located on a different worksheet.
Observed behavior: Attempting to use a structured reference for a cell on another worksheet (e.g., [@'Master Sheet'!C6]) results in a syntax error, preventing the conditional calculation from working.

Before you start

Verify the exact spelling of your worksheet name and ensure your data is formatted as an Excel Table so that structured references like [@CostPrice] function correctly.

Solution 1Recommended

Use an IF Function with Direct External Cell References

Fix the syntax error by combining standard absolute references for the external worksheet cell with structured references for the table columns.

Structured references (the ones using brackets like [@ColumnName]) are strictly for referencing data within the same Excel Table. If you try to add a worksheet name inside the brackets, Excel will return a syntax error. To resolve this, you must reference the external worksheet cell using standard cell referencing while keeping the structured references for your table calculations.

Select the target cell

Click on the first cell in your Excel Table where you want the calculated result to appear.

Enter the IF formula

Type the formula: =IF('Master Sheet'!$C$6="WGCRAWF",[@CostPrice]*[@MarkUp],([@CostPrice]+[@Loading])*[@MarkUp]).

Adjust formula references

Replace 'Master Sheet'!$C$6 with the actual sheet name and cell location of your customer code, and update 'WGCRAWF' to your specific customer identifier.

Apply the calculation

Press Enter. The Excel Table will automatically fill the formula down the entire column, calculating the standard price for most customers and the excluded-loading price for the specified customer.

Use Absolute References: Make sure to use absolute references (like $C$6 instead of C6) for the customer code cell. This ensures the formula continues to look at the exact same cell when it automatically fills down the table rows.

WPS Spreadsheet Solution

Calculate Conditional Costs Easily with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for structured table references and logical functions like IF. You can seamlessly perform complex cross-worksheet calculations without worrying about compatibility issues.

1. Format data as table: Open your workbook in WPS Spreadsheet, select your data range, and press Ctrl+T to format it as a Table.
2. Enter the pricing column: Click on the cell where you want to calculate the final price.
3. Input the cross-sheet formula: Type =IF('Master Sheet'!$C$6="WGCRAWF",[@CostPrice]*[@MarkUp],([@CostPrice]+[@Loading])*[@MarkUp]) and press Enter.
4. Review automated results: WPS Spreadsheet will instantly populate the calculated column, automatically applying the correct pricing logic based on the master sheet.

Fully compatible with Microsoft Excel formulas and structured table referencesFree to use for everyday data analysis and financial calculationsLightweight architecture ensures fast performance even with large datasetsFamiliar user interface makes formula auditing and editing simple

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a syntax error when using structured references for another sheet?

Structured references (e.g., [@CostPrice]) are designed exclusively for columns within the boundaries of the current Excel Table. Appending a sheet name inside the brackets violates the syntax rules. You must use standard cell references (e.g., 'Master Sheet'!C6) when pointing to data outside the table.

How can I exclude the loading fee for multiple different customers?

You can nest an OR function inside your IF statement. For example: =IF(OR('Master Sheet'!$C$6="CUST1", 'Master Sheet'!$C$6="CUST2"), [@CostPrice]*[@MarkUp], ([@CostPrice]+[@Loading])*[@MarkUp]). Alternatively, for a large list, use XLOOKUP or MATCH to check if the customer exists in an exemption list.

How do I stop an Excel table from automatically copying my formula down?

Immediately after pressing Enter to input your formula, click the Autocorrect Options button (a small lightning bolt icon) that appears next to the cell, and select 'Stop Automatically Creating Calculated Columns'.

Excel & Data Problems