Keep WPS Spreadsheets SUMIF and SUMIFS References Updated Across Workbooks
Question details
The user needs SUMIF or SUMIFS formulas to continue pulling data after regularly updated source workbooks are replaced.
- Product
- WPS Spreadsheets workbooks with SUMIF and SUMIFS formulas
- Device & OS
- not provided
- Scenario
- Source files such as deposito1 and deposito2 are replaced regularly, while a reporting workbook uses formulas to summarize their data.
- Observed behavior
- External formula references can stop updating when a source workbook name, location, or availability changes.
Confirm the source workbook names, worksheet names, and referenced columns are consistent before replacing files. If you plan to use INDIRECT, open every source workbook before recalculating the summary workbook.
Keep the source workbook name and folder path unchanged
Use this as the most reliable option when you replace source data on a regular schedule.
External SUMIF and SUMIFS references are less likely to break when the replacement workbook keeps the exact same file name, folder location, worksheet name, and column layout as the previous file.
Create a dedicated folder for source files, such as a Deposits folder, and keep the reporting workbook linked to files in that folder.
Replace the contents of files such as deposito1.xlsx and deposito2.xlsx without renaming the files or moving them to another folder.
In each source workbook, keep the worksheet name used by the formula, such as Sheet1, and retain the same data columns for criteria and amounts.
Open the reporting workbook in WPS Spreadsheets and use Formulas > Calculate Now, or press F9, after the source files are available.
Use INDIRECT with a workbook name stored in a cell
Use this when you need a formula to switch between named source workbooks dynamically and can keep the source workbooks open.
Import source data into a controlled reporting workbook
Use this when you need a repeatable refresh process without relying on formulas that point to frequently replaced workbooks.
Maintain dependable SUMIFS links and summaries with WPS Spreadsheets
WPS Spreadsheets lets you build SUMIF and SUMIFS summaries, maintain consistent external workbook links, and calculate updated results after source files are replaced. For dynamic workbook references, keep linked workbooks open when using INDIRECT.
- 1. Open the summary workbook: Launch WPS Spreadsheets and open the workbook that contains your SUMIF or SUMIFS report.
- 2. Keep source files consistent: Store source workbooks in a fixed folder and retain their expected names, such as deposito1.xlsx and deposito2.xlsx.
- 3. Update the formula links: Select a formula cell and use the formula bar to confirm that the linked workbook, sheet name, and referenced columns match the current source file.
- 4. Calculate updated results: Open the required source workbooks, then choose Formulas > Calculate Now or press F9 in the summary workbook.
Frequently Asked Questions
Why does my SUMIFS formula stop updating after I replace a source workbook?
The formula may still point to the old file path, workbook name, worksheet name, or range. Replace the file in the same folder with the same name and preserve the referenced sheet and column layout.
Can INDIRECT use a workbook name from a cell?
Yes. You can combine the workbook name in a cell with reference text, for example INDIRECT("["&A1&"]Sheet1!$E:$E"). The external workbook must be open for INDIRECT to return the reference.
Do source workbooks need to be open for normal external SUMIFS links?
Direct external links can generally retain previously saved values when a source workbook is closed, but updates are most dependable when the source workbook is available. INDIRECT specifically requires the external workbook to be open.
What must stay the same when I replace deposito1.xlsx?
Keep its folder location, file name, file type, worksheet name, and the columns used by your SUMIF or SUMIFS formulas unchanged.
How can I avoid external workbook links completely?
Import or consolidate the source records into a dedicated sheet in the reporting workbook, then update SUMIF or SUMIFS formulas to reference that local sheet.
