Use WPS Spreadsheets INDIRECT to Build Variable Formula Ranges
Question details
The user needs to create a formula whose referenced columns are controlled by column letters stored in cells.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Monthly calculations need to change their source range by updating starting and ending column letters in cells instead of rewriting the formula.
- Observed behavior
- The goal is for INDIRECT to convert cell text into a valid range reference, such as N5:P6, for use with AVERAGE.
Check that DF31 and DG31 contain valid column letters, such as N and P, without extra spaces. Confirm that the resulting range, such as N5:P6, exists in the worksheet and contains the values you want to calculate.
Build the dynamic range with INDIRECT and AVERAGE
Use this formula when the starting and ending column letters are stored in DF31 and DG31.
INDIRECT converts the text assembled from DF31, DG31, and the row numbers into a worksheet range. If DF31 contains N and DG31 contains P, the formula creates the reference N5:P6.
In WPS Spreadsheets, click the cell where you want to display the average of the variable range.
Type =AVERAGE(INDIRECT(DF31&"5:"&DG31&"6")) in the formula bar or directly in the selected cell.
Press Enter to calculate the result. WPS Spreadsheets reads the letters in DF31 and DG31 and averages the cells in the constructed range.
Replace the column letters in DF31 or DG31 with another valid starting or ending column. The formula updates the referenced range without requiring a formula edit.
Check the constructed reference when INDIRECT returns an error
Use these checks when the formula displays a reference error or does not calculate the expected range.
Create Flexible INDIRECT Formula Ranges in WPS Spreadsheets
WPS Spreadsheets lets you build dynamic formulas with INDIRECT, so cell-stored column letters can control monthly ranges without repeated formula editing. It supports familiar Excel-style functions and works with .xlsx files for easy sharing and migration.
- 1. Open a spreadsheet: Launch WPS Office and open an existing workbook or choose Spreadsheet from the new document screen.
- 2. Enter the range controls: Place the starting column letter in DF31 and the ending column letter in DG31, using values such as N and P.
- 3. Insert the dynamic formula: Select the result cell and enter =AVERAGE(INDIRECT(DF31&"5:"&DG31&"6")), then press Enter.
- 4. Update the calculation: Change the letters in DF31 or DG31 whenever the monthly source columns change, and WPS Spreadsheets will recalculate the result.
Frequently Asked Questions
What does the INDIRECT formula do in WPS Spreadsheets?
INDIRECT turns text into a cell or range reference. In =AVERAGE(INDIRECT(DF31&"5:"&DG31&"6")), the text from DF31 and DG31 is combined with row numbers to create a range such as N5:P6.
What should DF31 and DG31 contain?
DF31 should contain the starting column letter and DG31 should contain the ending column letter, such as N in DF31 and P in DG31. Avoid extra spaces and invalid characters.
Why does INDIRECT return a reference error?
The generated address is invalid, the column letters are incorrect, or the referenced cells do not exist on the worksheet. Check the values in DF31 and DG31 and verify the resulting range manually.
Can I use this formula for a different row range?
Yes. Change the row numbers in the text strings. For example, =AVERAGE(INDIRECT(DF31&"10:"&DG31&"12")) creates a range from the starting column at row 10 through the ending column at row 12.
