Why Excel Changes Form References When New Responses Arrive and How to Fix It in WPS Office
Question details
The user wants to understand why Excel changes form references when new responses arrive and how to make formulas keep returning the intended rows.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user maintains a Form response table in an Excel workbook and wants formulas on a separate worksheet to stay aligned when new responses are added.
- Observed behavior
- Excel automatically adjusts formula references because the source data is stored in a structured table that expands as new responses arrive, causing references to shift.
Open the workbook that contains the Form response table in WPS Office Spreadsheets. Identify the exact table name and the answer column header, because the formula uses these structured names.
Use INDEX and Structured References to Return Stable Form Values
Use this method when you want a destination sheet that consistently pulls the same row number from a Form response table, even after new submissions arrive.
Excel and WPS Office store Form responses in a structured table. Structured references such as Form[Answer] adjust automatically as the table grows, but a row-based INDEX formula can keep each destination row tied to a fixed position.
In WPS Office Spreadsheets, click the worksheet tab where you want to display the Form answers.
Click the cell in the second row where the first returned answer should appear.
Type =IFERROR(IF(INDEX(Form[Answer],ROWS($A$2:A2))="","",INDEX(Form[Answer],ROWS($A$2:A2))),"") and replace Form[Answer] with your table and column names.
Press Enter, then drag the fill handle down the column to return additional rows.
Add a Helper Column Inside the Forms Response Table
Use this method when you can modify the response table and want WPS Office or Excel to auto-fill a helper formula for every new response.
Keep Form References Stable with WPS Office Spreadsheets
Open your Excel Forms workbook in WPS Office Spreadsheets and use the same structured-reference and INDEX formulas to keep response references stable. WPS Office opens and saves .xlsx files without conversion, so your existing workbook and table structure remain intact.
- 1. Open the workbook in WPS Office Spreadsheets: Click File > Open in WPS Office Spreadsheets and select the Excel workbook that contains your Form response table.
- 2. Go to the destination worksheet: Click the destination sheet tab where you want the stable results to appear.
- 3. Enter the stable INDEX formula: Select the first result cell and type =IFERROR(IF(INDEX(Form[Answer],ROWS($A$2:A2))="","",INDEX(Form[Answer],ROWS($A$2:A2))),""), replacing Form and Answer with your real table and column names.
- 4. Fill the formula down: Press Enter, then use the fill handle to copy the formula down as far as needed.
- 5. Save the workbook: Click File > Save As and choose Excel Workbook (*.xlsx) to keep full Microsoft Office compatibility.
Frequently Asked Questions
Why does Excel change form references when new responses arrive?
Because Microsoft Forms stores responses in a structured table. When the table expands, Excel adjusts ordinary cell references, which can shift formulas to different rows. Using structured references or INDEX with ROWS keeps the intended row stable.
Does the INDEX formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports structured references such as Form[Answer] and the INDEX function, so you can use the same formula in an .xlsx workbook.
How do I find the correct table name and column name?
Click anywhere inside the response table, then open the Table Tools tab in WPS Office Spreadsheets and check the table name box. The column header is the name shown in the first row of that column.
Can I add a helper column to the Forms response table?
Yes. You can add a helper column to the response table. Use a formula such as =IF([@Answer]="","",[@Answer]) in the second row; the table will fill it down and apply it to new rows.
Will WPS Office save my workbook in Excel format?
Yes. Use File > Save As and choose Excel Workbook (*.xlsx) to keep the file compatible with Microsoft Excel.
