How to Use the Same Excel Formula Across More Than 40 Worksheets in WPS Office
Question details
The user needs to calculate minimum, maximum, previous maximum, and next readings when each location is stored on a separate worksheet in the same workbook.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a workbook with more than 40 location worksheets and wants to apply the same calculation consistently without editing each sheet individually.
- Observed behavior
- Manually entering or maintaining formulas across more than 40 separate worksheets is inefficient and prone to reference errors.
Confirm that all location worksheets follow the same layout and note the exact cell or range reference you need to calculate, such as B2:B100 or C5.
Apply a Dynamic INDIRECT Formula Using a Worksheet Name List
Use this when sheet names are predictable or can be listed and you need the same calculation to update automatically as source sheets change.
INDIRECT turns a worksheet name stored in a cell into a usable reference, so you can write one formula and copy it down a summary list instead of editing 40 sheets.
This method works best when each location sheet has the same cell layout and the worksheet names are managed in a dedicated list.
In a summary worksheet, create a column containing the exact sheet names for all 40+ location tabs, for example A2:A45.
In B2, enter a formula such as =MIN(INDIRECT("'"&A2&"'!B2:B100")) and press Enter to calculate the first location sheet.
Drag the fill handle from B2 down through the whole worksheet name list to apply the same calculation across all location sheets.
Create adjacent columns with similar INDIRECT formulas, adjusting the calculation to MAX, previous maximum, or next reading as needed.
Consolidate Data with Power Query Before Calculating Statistics
Use this when you have many sheets or large data ranges and prefer a cleaner summary table that is easier to audit and expand.
Use WPS Office Spreadsheets to Apply One Formula Across 40+ Worksheets
WPS Office Spreadsheets supports dynamic INDIRECT formulas and Power Query-style consolidation so you can calculate minimum, maximum, previous maximum, and next readings across many location sheets without editing every tab.
- 1. Open your workbook: Open the multi-sheet workbook in WPS Office Spreadsheets.
- 2. Create a worksheet name list: In a summary sheet, list all location worksheet names in one column.
- 3. Enter the INDIRECT formula: Use =MIN(INDIRECT("'"&A2&"'!B2:B100")) or the needed calculation and copy it down.
- 4. Review the calculated readings: Check minimum, maximum, previous maximum, and next reading columns for each location sheet.
- 5. Save the workbook: Save the workbook in Microsoft Excel .xlsx format to preserve formulas and share it with Excel users.
Frequently Asked Questions
Can I use the same formula across more than 40 worksheets without typing it into each sheet?
Yes. Put the sheet names in a summary column and use INDIRECT to create a dynamic reference, then copy the formula down the list.
How do I calculate minimum and maximum readings when each location is on a separate worksheet?
Use INDIRECT with a sheet name list, for example =MIN(INDIRECT("'"&A2&"'!B2:B100")) for minimum and =MAX(INDIRECT("'"&A2&"'!B2:B100")) for maximum, then fill down.
Does WPS Office support Power Query consolidation like Excel?
WPS Office includes Data tools for importing and consolidating same-layout ranges; use the Data tab and Power Query/Get Data options if available, then load the appended table into a summary sheet.
What should I do if my worksheet names contain spaces?
Keep single quotes around the sheet name inside the INDIRECT reference: =INDIRECT("'"&A2&"'!B2:B100").
Can I use 3D references across all location sheets instead?
3D references work when the same cell or range exists on adjacent sheets, but for 40+ sheets with a dynamic sheet list, INDIRECT or Power Query consolidation is usually more flexible.
