Spreadsheet Formula Sorting With Reference Preservation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current spreadsheet applications corrupt formulas when sorting cells that contain references to other cells, leading to incorrect recalculations and loss of formula functionality.
Innovation Solution
The technology updates referenced cells post-sort to retain their pre-sort relationships and adjusts formulas to avoid corruption, handling single and multiple column sorts, including formulas with cell data, Non-spreadsheet cell (NSC) external data, and combinations thereof.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If spreadsheet cells containing formulas are sorted, then the sorting function works correctly, but the formulas become corrupted and recalculate incorrectly
Solution Approach 1:
The system performs preliminary actions by capturing the pre-sort state of formula cells and their references before sorting occurs. It stores the original formula expressions, calculated values, and cell reference relationships in a temporary state, then uses this captured information to restore formulas to their correct pre-sort values after the sort operation completes.
Solution Approach 2:
The system introduces an intermediary mechanism that acts as a buffer between the sort operation and the formula cells. This intermediary captures the pre-sort formula states and uses them to reconstruct the correct values post-sort, preventing direct corruption of formulas while allowing the sort to proceed.
2Reliability
If formulas are converted to values to retain correct values after sort, then the values remain correct, but the formulas are lost and future change flexibility is lost
Solution Approach 1:
The system creates a copy of the pre-sort formula states and stores them separately from the sorted data. This copy contains the original formula expressions and reference relationships, allowing the system to restore formulas after sorting without permanently converting them to static values, thus preserving both correctness and flexibility.
Solution Approach 2:
The system changes the state parameter of formula cells dynamically - maintaining them in formula state before sort, switching to value state during sort to prevent corruption, then restoring to formula state after sort with the correct pre-sort values. This parameter switching allows the system to achieve both correctness during sort and flexibility afterward.
3Reliability
If cell references are updated post-sort to retain pre-sort relationships, then formula values remain correct, but the system complexity increases
Solution Approach 1:
The system implements self-service by automatically tracking and managing formula states without requiring user intervention. It autonomously captures pre-sort states, performs the sort operation, and restores formulas to their correct values, making the complexity transparent to the user while maintaining simplicity in usage.
Data Source
AI summary
The technology disclosed relates to accessing data within or external to spreadsheet cells (NSC data) and presenting it to users, so they can select one or more value to populate the cell. In one implementation, the technology disclosed is a spreadsheet function (predefined formula) where the user inputs a cell range or NSC data formula resulting in a button operated drop-down (pop-up or other visual) populating one value within the cell. Once the formula is completed the user can change that value by clicking the button and selecting the desired value from the multiple values presented. In another implementation the function input can be altered to add a constraint (filter) limiting the values presented for selection. In variants of any of the implementations, the presented values can be organized in ascending or descending orders for easy selection and show all values or only the unique (distinct) values.