Spreadsheet Cell Reference Tracking for Organized Tables

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Formulas referencing cell ranges become noncontiguous and incorrect when a spreadsheet table is updated or organized, such as through grouping or categorization, leading to unintended changes in cell references.

Innovation Solution

Implementing a system to track and update cell references using a coordinate system or index, and introducing a summary reference cell to maintain accurate formula references even after table reorganization, ensuring formulas correctly reference the intended cells regardless of rearrangements.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If a table is updated or organized through grouping or categorization, then the table structure is improved and data is better organized, but cell references in formulas become noncontiguous and incorrect

Engineering Contradiction:

Improvetable organization flexibilityVSAvoidformula reference accuracy

Core Design Contradiction:

Adaptability or versatilityVSReliability

Solution Approach 1:

The system performs preliminary tracking of cell references before table reorganization occurs. By establishing the mapping between original cell positions and new positions in advance, the system can proactively update formulas to maintain correct references even as the table structure changes through grouping or categorization operations.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system implements a feedback mechanism where cell reference changes are detected and monitored during table reorganization. When grouping or categorization operations are performed, the system automatically detects the resulting noncontiguous references and corrects them by updating formulas to reference the intended cells, ensuring continuous accuracy.

Inventive Principle:

Principle #23Feedback

2Adaptability or versatility

If formulas reference cell ranges dynamically, then formulas remain flexible after table changes, but the complexity of tracking and updating references increases

Engineering Contradiction:

Improveformula reference flexibilityVSAvoidreference tracking system complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The system introduces an intermediary layer that sits between the table structure and formula references. This intermediary maintains a mapping system that translates dynamic cell positions into stable reference identifiers, allowing formulas to remain simple while the underlying tracking mechanism handles the complexity of dynamic reference updates automatically.

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS10657321B2Exploded-range references

Publication Date: 2020.05.19 APPLE INC

AI summary

Systems, methods, and devices for tracking spreadsheet table cells from a base table to an organized table/organized table view, and updating of a formula reference based on the tracked cells are provided. In some embodiments, cells may be tracked to convert a first range of cells in the base table into a second range of cells in the organized table/organized table view, such that the second range of cells includes the same cells as the first range of cells, despite any reorganization of rows and columns. In some embodiments, a summary reference cell that provides a sub-group summary based upon the grouping within the organized table/organized table view may also be provided.