Function Problems

How to Dynamically Change a Formula Column Reference in Excel

Kushani Nimanthika Oct 10, 2026 869 views

Question details

The user needs to make an Excel formula's column reference dynamic by pulling the column letter from another cell.

How to Dynamically Change a Formula Column Reference in Excel

Product: Excel
Device & OS: not provided
Scenario: Updating cell references automatically without manually rewriting formulas when the target column changes.
Observed behavior: The current formula uses a static, hard-coded cell reference (e.g., ='Project Staff'!$J$7) that does not update dynamically when the user wants to point to a different column.

Before you start

Ensure you have the exact sheet name and understand which cell will dictate the dynamic column letter before writing your formula.

Solution 1Recommended

Use the INDIRECT Function to Build Dynamic References

The INDIRECT function converts a text string into a valid cell reference, allowing you to build dynamic formulas using variables from other cells.

The INDIRECT function is highly effective for dynamically changing column or row references. However, it is a volatile function, meaning it recalculates every time a change occurs anywhere in the open workbook. While this may not matter in a small workbook, it can reduce calculation performance in complex files.

Identify the reference components

Determine the fixed parts of your cell reference (such as the sheet name and row number) and the dynamic part (the column letter).

Set up the control cell

Type the desired column letter (for example, 'J') into a designated cell, such as C2. This cell will control the dynamic reference.

Construct the INDIRECT formula

Click on the cell where you want the formula result. Type the formula: =INDIRECT("'Project Staff'!"&C2&"7") and press Enter.

Test the dynamic update

Change the value in cell C2 from 'J' to 'K'. The formula will automatically update to retrieve data from ='Project Staff'!K7.

Use the INDIRECT Function to Build Dynamic References

Performance Tip: Because INDIRECT is volatile, avoid excessive use in massive workbooks. For very large data sets, consider non-volatile alternatives like the INDEX function.

Advanced Formula Support

Easily Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like INDIRECT, allowing you to build dynamic and automated reports with ease. It provides a highly compatible environment for all your data analysis and formula needs.

1. Open WPS Spreadsheet: Launch WPS Office and open your target workbook or .xlsx file.
2. Input the Formula: Select your target cell and type =INDIRECT() to see instant syntax hints.
3. Build the String: Concatenate your text strings and cell references inside the parentheses, for example: =INDIRECT("'Sheet1'!"&A1&"10").
4. Apply and Calculate: Press Enter to execute. The result will instantly update whenever your reference cell changes.

Fully compatible with Microsoft Excel formulas and .xlsx files.Seamlessly handles dynamic reference functions like INDIRECT and OFFSET.Lightweight architecture ensures smooth performance even with complex calculations.Built-in function syntax suggestions to help you construct strings accurately.

microsoft office alternative - wps office

Frequently Asked Questions

Can I make both the row and column dynamic in my formula?

Yes. You can store the column letter in one cell (e.g., C2) and the row number in another (e.g., D2), then use =INDIRECT("'Project Staff'!"&C2&D2) to make the entire cell reference dynamic.

Why am I getting a #REF! error when using the INDIRECT function?

A #REF! error usually occurs if the text string inside the INDIRECT function does not form a valid cell reference, if there is a typo in the sheet name, or if the formula refers to a closed external workbook.

Is there a non-volatile alternative to INDIRECT for dynamic columns?

Yes, you can use the INDEX and MATCH functions combined. While slightly more complex to set up, INDEX is non-volatile and generally performs much better in large workbooks with extensive calculations.

Do I need to include single quotes around the sheet name in the INDIRECT formula?

Single quotes are strictly required if your sheet name contains spaces, such as 'Project Staff'. If the sheet name has no spaces (like Sheet1), the quotes can be omitted, but including them is a safe standard practice.

Excel & Data Problems