Update Formulas Across Monthly Excel Worksheets Automatically in WPS Spreadsheets
Question details
The user needs to maintain inventory formulas across 12 monthly worksheets and automatically reference the previous month's worksheet instead of editing each formula manually.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains 12 monthly inventory sheets with the same formulas, and the user wants updates or previous-month references to carry across sheets automatically.
- Observed behavior
- Formulas must be manually changed on each sheet, making the workbook hard to maintain.
Before you start, check whether your 12 monthly sheets have the same layout and column positions. If they do, you can either add a Month column and consolidate the data, or use a helper cell with the previous sheet name for INDIRECT formulas.
Consolidate monthly inventory data into one table and create a PivotTable
Best when you can change the workbook structure: it removes the need to edit formulas on each monthly sheet.
Storing all inventory transactions in one table with a Month or Date column is the most maintainable design. WPS Spreadsheets can create PivotTable reports that show any month or compare months without copying formulas across sheets.
In WPS Spreadsheets, open your workbook and create a sheet named Data. Enter columns such as Item, Category, Quantity, Date, and Month, then paste or import the inventory records from the monthly sheets into this table.
Click any cell inside the data table, then go to the Insert tab and click PivotTable.
In the Create PivotTable dialog, confirm the table/range and choose New Worksheet, then click OK.
In the PivotTable Field List, drag Month to the Rows or Filters area and drag Quantity or Inventory Value to the Values area. Use the Month filter or row labels to switch between monthly views instead of editing formulas.
Use a helper cell and INDIRECT to reference the previous month's worksheet
Use when you must keep separate monthly sheets and want formulas to update by changing one sheet-name cell.
Automate Monthly Inventory Reports in WPS Spreadsheets
WPS Spreadsheets helps you replace manual monthly formula edits with a single consolidated data table and PivotTable reports. You can also use INDIRECT for separate monthly sheets, all in a familiar spreadsheet interface.
- 1. Open your workbook in WPS Spreadsheets: Download and install WPS Office, then open the existing .xlsx or .xls inventory workbook in WPS Spreadsheets.
- 2. Create a single data table: Add a new sheet named Data and consolidate the 12 monthly inventory sheets into one table with a Month or Date column.
- 3. Insert a PivotTable report: Click inside the data table, go to Insert > PivotTable, choose New Worksheet, and click OK.
- 4. Configure monthly fields: Drag Month to the Rows or Filters area and drag the inventory value field to the Values area to show monthly totals automatically.
- 5. Refresh when data changes: After adding transactions, right-click the PivotTable and select Refresh to update all monthly reports.
Frequently Asked Questions
Can I reference the previous month's worksheet automatically in WPS Spreadsheets?
Yes. If you keep separate monthly sheets, enter the previous sheet name in a helper cell and use a formula such as =INDIRECT("'"&A1&"'!C1"). The formula updates when the helper cell changes, but INDIRECT is volatile and can slow large workbooks.
What is the best way to avoid editing the same formulas on 12 monthly sheets?
Consolidate all inventory records into one data table with a Month or Date column, then create a PivotTable report in WPS Spreadsheets. You can switch months with the PivotTable filter instead of maintaining formulas on each sheet.
How do I create a PivotTable in WPS Spreadsheets?
Click anywhere in your data range, go to the Insert tab, click PivotTable, confirm the table/range, choose New Worksheet, and click OK. Then drag fields such as Month and Quantity into the PivotTable Field List.
Why does my INDIRECT formula return #REF! when referencing another sheet?
Usually the sheet name is missing quotation marks, the helper cell contains the wrong name, or the referenced cell does not exist. Use the format =INDIRECT("'"&A1&"'!C1") and check that the helper cell exactly matches the sheet name.
Can WPS Office open a workbook with formulas and PivotTables created in Microsoft Excel?
Yes. WPS Spreadsheets opens .xlsx and .xls workbooks and supports Excel-style formulas, PivotTables, and common data analysis features, so you can continue working on your monthly inventory workbook.
