How to Update a Sorted Excel Schedule Without Circular References in WPS Office
Question details
The user needs to update a sorted Excel schedule, but circular references prevent SORTBY and other dynamic array formulas from recalculating correctly when schedule changes are stored in iterative formulas.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a sorted schedule in an Excel-compatible workbook and stores schedule changes in the same cells that formulas are trying to calculate, creating circular references.
- Observed behavior
- SORTBY and other dynamic array formulas do not recalculate correctly because the schedule change cells are involved in circular references.
Open the workbook in WPS Office Spreadsheets and use Formulas > Error Checking > Circular References to see which schedule cells are involved. Identify whether the same cells are being used for both schedule inputs and formula results before restructuring.
Restructure the Schedule with a Separate Input Table and Output Formulas
Use this when SORTBY, SORT, or FILTER stops recalculating because schedule cells are both inputs and formula outputs.
Circular references occur when a formula reads from the same range it is trying to update. A reliable scheduling model separates the raw schedule data from the generated schedule output, so dynamic array formulas can recalculate cleanly.
In WPS Office Spreadsheets, click the plus (+) icon next to the sheet tab, name the new sheet Inputs, and enter schedule items with columns such as Date, Task, Priority, and Change.
Select the current sorted schedule cells, press Delete to clear formulas that reference their own output cells, and leave the range empty for the generated schedule.
Click the first output cell and enter =SORTBY(Inputs!A2:D100, Inputs!C2:C100, 1) or =SORT(FILTER(Inputs!A2:D100,Inputs!D2:D100<>""),3,1) to generate the sorted schedule from the input table.
Add or move tasks in the input table and verify that the output schedule recalculates without the circular reference warning.
Locate Existing Circular References and Switch Calculation Mode
Use this before you restructure the workbook to stop repeated calculation prompts and identify which cells are self-referencing.
Build a Circular-Reference-Free Sorted Schedule in WPS Office Spreadsheets
WPS Office Spreadsheets lets you keep schedule inputs separate from the sorted output, so SORTBY, FILTER, and SORT recalculate reliably without circular references while preserving the workbook you already use.
- 1. Open your schedule workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the .xlsx schedule file.
- 2. Add a dedicated input sheet: Right-click the sheet tab, choose Insert Sheet, name it Inputs, and enter the schedule data and change columns.
- 3. Replace circular cells with output formulas: In the original schedule sheet, delete self-referencing formulas and enter =SORTBY(Inputs!A2:D100,Inputs!C2:C100,1) or a similar dynamic array formula.
- 4. Check for remaining circular references: Go to Formulas > Error Checking > Circular References and resolve any remaining self-references.
- 5. Save and test the schedule: Press Ctrl+S to save and enter several schedule changes to confirm the sorted output recalculates correctly.
Frequently Asked Questions
Why does SORTBY still show a circular reference after I update a sorted Excel schedule?
Because at least one cell in the SORTBY range depends on its own calculated result. In WPS Office Spreadsheets, keep the change records in a separate input table and let the sorted schedule be generated only by formulas.
Can I use FILTER and SORT together to rebuild a schedule without circular references?
Yes. Use a clean input range and enter a formula such as =SORT(FILTER(Inputs!A2:D100, Inputs!D2:D100<>""), 3, 1). The output range must not contain the source values that the formula reads.
How do I find the cell causing a circular reference in WPS Office?
Go to Formulas > Error Checking > Circular References. The menu lists the circular cells; select each one to jump to the location and then move its input to a helper column or sheet.
Will this restructuring keep my file compatible with Microsoft Excel?
Yes. The workbook stays an .xlsx file, and SORTBY, SORT, and FILTER results remain readable in Microsoft Excel when the file is opened there.
What if I use Excel for the web and cannot run VBA?
Use the formula and input-table restructure instead of VBA. Excel for the web does not run VBA, while the separated input/output model works across WPS Office and Excel for the web.
