Function Problems

How to Build a Dynamic Excel Range from Cell Values

Huma Ashraf Ch Oct 10, 2026 869 views

Question details

The user needs to construct a dynamic Excel range reference using text strings and numerical values stored in different cells.

How to Build an Excel Range from Cell Values

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic formulas where the range references need to update based on variable inputs in specific cells.
Observed behavior: The text string combination needs to be converted into a valid range reference that functions like SUM can process.

Before you start

Ensure the cells you are referencing contain valid row numbers, column letters, or worksheet names before combining them into a range.

Solution 1Recommended

Use the INDIRECT Function to Build the Range

Use the INDIRECT function to convert concatenated text strings from specific cells into a valid Excel range.

The INDIRECT function evaluates a text string as a valid cell reference. By using the ampersand (&) operator, you can stitch together column letters and row numbers stored in different cells to form a dynamic range.

Identify input cells

Assume cell A1 contains the column letter (e.g., 'P'), A2 contains the starting row (e.g., '12'), and A3 contains the ending row (e.g., '16').

Combine the text strings

Construct the text string using the ampersand operator: A1&A2&":"&A1&A3. This visually creates the text string 'P12:P16'.

Wrap in INDIRECT

Place the combined string inside the INDIRECT function: INDIRECT(A1&A2&":"&A1&A3).

Use inside another function

Wrap the INDIRECT formula within a calculation function like SUM. The final formula will look like: =SUM(INDIRECT(A1&A2&":"&A1&A3)).

Use the INDIRECT Function to Build the Range

Reference other worksheets: You can also include worksheet names dynamically. For example: =INDIRECT("'" & A1 & "'!" & B1 & ":" & C1) where A1 holds the sheet name.

WPS Spreadsheet Solutions

Create Dynamic Ranges Easily with WPS Spreadsheet

WPS Spreadsheet fully supports the INDIRECT function and complex array formulas, allowing you to build dynamic, flexible reports seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the WPS Spreadsheet application.
2. Input the formula: Select the target cell and type your calculation formula containing the INDIRECT function and cell concatenations.
3. Execute the calculation: Press Enter to execute the function. Your dynamically built range will instantly calculate.

Fully compatible with Microsoft Excel formulas and functionsSupports dynamic range referencing using INDIRECT and text concatenationLightweight application with high performance for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT function returning a #REF! error?

The #REF! error occurs if the concatenated text does not form a valid Excel cell reference, or if a referenced external worksheet is closed. Double-check for typos and ensure any referenced sheet names are spelled correctly.

Can I use INDIRECT to reference another workbook?

Yes, but the referenced external workbook must be open in the background. If the external workbook is closed, the INDIRECT function will automatically return a #REF! error.

How do I include spaces in worksheet names when using INDIRECT?

If the worksheet name contains spaces, you must enclose the sheet name in single quotes within your text string. For example, if cell A1 contains the sheet name, use: =INDIRECT("'" & A1 & "'!A1:B10").

Excel & Data Problems