Function Problems

How to Fix Excel INDIRECT #REF Error with Structured Table References

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to resolve an issue where using an INDIRECT formula with a structured table reference in data validation returns a #REF! error.

Product: Excel
Device & OS: not provided
Scenario: Setting up a data validation dropdown list using data from a structured table named 'Category'.
Observed behavior: A direct formula like =Category[Category] successfully returns values, but wrapping the structured reference in an INDIRECT function results in a #REF! error.

Before you start

Verify that your source data is formatted as an official Table (Insert > Table) and that the table name and column headers exactly match the text inside your formula.

Solution 1Recommended

Fix Data Validation Syntax for Entire Columns

Correct the string formatting inside the INDIRECT function to properly reference the entire table column for a dropdown list.

When using INDIRECT for data validation, the structured reference must be passed as a pure text string. Including an extra equals sign inside the quotation marks or omitting the quotes will cause the application to fail to parse the table name, resulting in a #REF! error.

Select Target Cell

Click on the cell or range of cells where you want to insert the dropdown list.

Open Data Validation

Navigate to the Data tab on the ribbon and click on 'Data Validation'.

Apply Correct Syntax

Under the Settings tab, choose 'List' from the Allow dropdown. In the Source box, enter exactly: =INDIRECT("Category[Category]"). Ensure there are no equals signs inside the quotation marks.

Syntax check: The outer equals sign tells the program it is a formula, while the quotation marks tell the INDIRECT function to evaluate the text string "Category[Category]" as a valid range.

Solution 2

Extract Current Row Values Using the @ Symbol

Use this method when you need the INDIRECT formula to return only the value corresponding to the current row, rather than the entire column array.

Use WPS Spreadsheet

Easily Create Dynamic Dropdowns with WPS Spreadsheet

WPS Spreadsheet fully supports structured table references and advanced formulas like INDIRECT, allowing you to seamlessly create dynamic data validation lists without syntax compatibility issues.

1. Format Data as Table: Highlight your data in WPS Spreadsheet and press Ctrl+T to create a structured table.
2. Open Data Validation: Select the target cell, go to the Data tab, and click Data Validation.
3. Enter the Formula: Select 'List' and type your INDIRECT formula with quotation marks to generate the dynamic list.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Intuitive Data Validation menu for quick dropdown list creation.Clear syntax highlighting to help you avoid #REF! errors.Free and lightweight alternative for all your spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error when referencing a table?

This error typically occurs if the text string inside the INDIRECT function is formatted incorrectly, such as including an accidental equals sign inside the quotes, missing the quotation marks entirely, or misspelling the table or column name.

What is the purpose of the @ symbol in a structured table reference?

The @ symbol, such as in [@Category], is an implicit intersection operator. It tells the formula to retrieve only the single value from the specified column that is in the exact same row as the formula, rather than returning the entire column's data.

Can I use direct table references in data validation without INDIRECT?

In newer spreadsheet versions, you can sometimes use direct references like =Category[Category] in data validation. However, wrapping it in INDIRECT is a reliable workaround for dynamic ranges or when backward compatibility with older file formats is required.

Excel & Data Problems