Fix Excel INDEX Formula Not Updating After Macro Refresh in WPS Spreadsheets
Question details
The user needs an INDEX formula in a WPS Spreadsheets workbook to display data copied by a macro after the formula stops updating due to Manual calculation mode or an outdated external reference.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A macro copies data into a workbook, but the INDEX formula referencing the copied range continues to show stale results.
- Observed behavior
- The INDEX formula does not refresh after the macro copy until recalculation is forced or the formula references are corrected.
Before editing the formula, open Formulas > Calculation Options and note whether calculation is set to Automatic or Manual. Also identify the exact worksheet and cell range the macro writes to so you can compare them with the INDEX formula reference.
Set Calculation Options to Automatic and Recalculate the Workbook
Use this solution when the workbook is in Manual calculation mode and the INDEX formula stops updating after a macro runs.
Manual calculation is often enabled in large workbooks or workbooks with external references to avoid slow recalculation, but it prevents formulas from refreshing automatically after macro copy operations.
In WPS Spreadsheets, click the Formulas tab and select Calculation Options.
Choose Automatic from the Calculation Options menu so formulas recalculate whenever dependent cells change.
Press F9 to recalculate the entire workbook, or press Shift+F9 to recalculate only the active worksheet.
Check the INDEX formula cell after recalculation to confirm it now displays the latest values copied by the macro.
Verify Macro Range, INDEX Reference, and External Workbook Links
Use this solution when calculation is already Automatic but the INDEX formula still does not return the copied data.
Fix INDEX Formula Calculation in WPS Spreadsheets
WPS Spreadsheets can directly resolve INDEX formula update issues by letting you switch calculation to Automatic, recalculate after macro operations, and save workbooks in Excel-compatible formats.
- 1. Install WPS Office: Download WPS Office from the official WPS website and install it on your computer.
- 2. Open the workbook: Launch WPS Spreadsheets and open the .xlsx or .xlsm workbook that contains the INDEX formula and macro.
- 3. Enable Automatic calculation: Click the Formulas tab, open Calculation Options, and select Automatic.
- 4. Recalculate after macro refresh: Press F9 to recalculate the whole workbook or Shift+F9 for the active worksheet after the macro copies data.
- 5. Save in Excel-compatible format: Save the file as .xlsx or .xlsm to preserve formulas, macros, and workbook structure.
Frequently Asked Questions
Why does my INDEX formula not update after a macro runs in WPS Spreadsheets?
The workbook is usually set to Manual calculation. Click Formulas > Calculation Options > Automatic, then press F9 to recalculate the workbook.
How do I recalculate only the active worksheet instead of the whole workbook?
Press Shift+F9 to recalculate the active worksheet. Press F9 to recalculate the entire workbook.
Will switching to Automatic calculation slow down my workbook?
It can slow down large workbooks with many formulas or external links. If you keep Manual calculation enabled, press F9 after macro operations to refresh formulas.
What should I check if the INDEX formula is still not updating after recalculation?
Confirm the macro writes to the same worksheet and range referenced by INDEX, update external links through Data > Edit Links, and add Application.Calculate to the macro after copying.
Can I make VBA recalculate automatically after copying data?
Yes. Add Application.Calculate or a worksheet-specific calculate command such as Worksheets("Sheet1").Calculate after the copy operation in the macro.
