Dynamic Spreadsheet Arrays with Spill and Collapse
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current spreadsheet applications treat arrays as independent values without indicating relationships between them, making it difficult to create and reference arrays effectively, especially when the number of values changes, and there is no mechanism to resolve a formula to multiple values without occupying neighboring cells.
Innovation Solution
A computer-implemented method and system for creating and referencing arrays in a spreadsheet, allowing users to select values and create arrays, resolve formulas to arrays, and dynamically update or collapse arrays, enabling intuitive array creation and referencing across multiple cells while maintaining calculation capabilities.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If a formula resolves to multiple values in current spreadsheet applications, then some values must occupy neighboring cells, but this disrupts the compact structure and makes array management difficult
Solution Approach 1:
The patent introduces a new dimensional layer by allowing arrays to exist as independent objects that can be referenced from a single cell, rather than being constrained to two-dimensional cell grids. This enables formulas to return multiple values without occupying neighboring cells in the traditional sense, as the array lives in a separate dimensional space that can be invoked on-demand.
Solution Approach 2:
The patent introduces an array object as an intermediary between the formula and the cell display. Instead of directly placing multiple values in neighboring cells, the formula creates an array object that is then referenced by a single cell, acting as a mediator that holds the multi-value result without disrupting the cell structure.
2Ease of manufacture
If arrays are treated as independent values without relationship indicators, then array creation is simple, but there is no mechanism to dependably reference the values as a whole
Solution Approach 1:
The patent merges the concept of independent values with collective referencing by creating an array object that contains all values while providing a single unified reference point. The array combines multiple values into one cohesive structure that can be referenced as a whole, maintaining both simplicity and reliability.
Solution Approach 2:
The array object serves multiple functions: it stores multiple values, provides a single reference point, enables bulk operations, and maintains individual value access. This multi-functionality allows the same structure to satisfy both simple creation needs and reliable reference requirements.
3Productivity
If multiple values are returned by a formula, then the values are calculated, but there is no indication that those values are related
Solution Approach 1:
The patent uses visual indicators (such as spillover effects and highlighting) to change the appearance of cells containing array values, signaling to users that these values are related and form part of a larger array structure. This visual coding preserves relationship information that would otherwise be lost.
Solution Approach 2:
The array object acts as an intermediary that carries relationship information between the formula and the displayed values. It maintains the connection between multiple values and provides visual and structural indicators that these values are related, preventing loss of relationship information.
4Adaptability or versatility
If arrays are dynamically resized, then the array adapts to data changes, but existing references may become invalid
Solution Approach 1:
The patent implements dynamic arrays that can automatically resize based on data changes while maintaining stable references through named ranges and structured references. The array structure is dynamic, but the reference mechanism is designed to adapt to size changes, allowing both flexibility and stability.
Solution Approach 2:
The system provides feedback mechanisms that monitor array size changes and automatically update references accordingly. When an array resizes, the system detects this change and adjusts existing references to maintain their validity, creating a feedback loop that preserves reference stability during dynamic changes.
Data Source
AI summary
The present disclosure provides a novel approach for creating and referencing arrays as implemented in a spreadsheet application. Aspects of the present disclosure include, but are not limited to: referencing arrays, creating dynamic arrays, collapsing values of an array into a single cell, spilling values of an array to a table, referencing one or more values in collapsed arrays, visually indicating no spill errors while maintaining calculation capabilities thereon, and implicit intersection of data.