SharePoint Document Issues

How to Fix a SharePoint List Column Formula That Is Not Working

Algirdas Jasaitis Sep 30, 2026 868 views

Question details

The user wants to display specific text values based on the numerical value of another column in a SharePoint list, but the formula returns an error.

How to Fix a SharePoint List Column Formula That Is Not Working

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Creating a conditional rule in a SharePoint list to display 'Nominal' or 'Order' based on a 'QTY' column.
Observed behavior: SharePoint column validation formulas cannot calculate a value based on another column, resulting in errors or a failure to save the formula.

Before you start

Ensure you have the necessary permission levels, such as Edit or Design, to add or modify columns in the target SharePoint list.

Solution 1Recommended

Use a Calculated Column Instead of Column Validation

Column validation restricts data entry and cannot reference other columns. To output a dynamic value based on another column's data, you must create a Calculated column.

SharePoint explicitly restricts validation formulas from referencing other columns. By creating a Calculated column instead, you can use Excel-like formulas to evaluate data from other fields and dynamically display text values such as 'Nominal' or 'Order'.

Add a new column

Navigate to your SharePoint list, click on 'Add column', select 'More...', and choose 'Calculated (calculation based on other columns)' as the data type.

Name the column

Enter a name for your new Calculated column, such as 'ORDER MORE'.

Enter the IF formula

In the Formula box, input the following formula to handle the logic and ignore blank values: =IF(ISBLANK([QTY]),"",IF([QTY]>10,"Nominal","Order"))

Set the returned data type

Under 'The data type returned from this formula is', select 'Single line of text' and click 'OK' to save your new column.

Use a Calculated Column Instead of Column Validation

Verify Column Names: If SharePoint displays a syntax error after saving, verify that the internal column name exactly matches what is written in the brackets in your formula (e.g., [QTY]). Depending on your regional settings, you may also need to replace commas with semicolons in the formula.

Free Microsoft Office alternative

Manage and Analyze Your Exported SharePoint Data with WPS Office

While SharePoint handles basic list structures online, you might often need to export these lists to manage, analyze, or format data locally. WPS Spreadsheet provides a lightweight, highly compatible alternative to Microsoft Excel, allowing you to use all your familiar formulas to process list data for free.

1. Export SharePoint List: Click 'Export to Excel' or 'Export to CSV' in your SharePoint list menu to download your data.
2. Open with WPS Spreadsheet: Launch WPS Office and open the downloaded spreadsheet file to view your SharePoint data offline.
3. Apply Formulas: Use familiar formulas like =IF() and =ISBLANK() directly in WPS Spreadsheet to analyze your list data without SharePoint's formula restrictions.

Fully compatible with Microsoft Excel (.xlsx and .csv) formats exported from SharePoint.Supports advanced IF, ISBLANK, and conditional formulas identical to Microsoft Excel.Free and lightweight, running smoothly on Windows, Mac, Linux, iOS, and Android.Familiar user interface requiring zero learning curve for Microsoft Office users.

Frequently Asked Questions

Can I use Excel formulas in a SharePoint Calculated column?

Yes, SharePoint Calculated columns support a wide range of standard Excel formulas, including IF, ISBLANK, CONCATENATE, and basic date functions. However, complex array formulas or newer Excel functions (like XLOOKUP) are not supported in SharePoint lists.

Why am I getting a syntax error when saving my SharePoint formula?

Syntax errors typically occur if a column name is misspelled, if you forgot the square brackets around the column name (e.g., [QTY]), or if you used the wrong list separator. In some regions, SharePoint requires semicolons (;) instead of commas (,) to separate arguments in a formula.

Can I use a Calculated column to automatically update another column's value?

No, Calculated columns can only compute and display a result based on other columns in the same list item. They are read-only and cannot push or write data into a different column. For that, you would need to use Power Automate.

Document & File Issues