Spreadsheet Overlay for Suspect Cell Detection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Complex and large spreadsheet programs face challenges in ensuring calculation accuracy due to subtle errors, such as typographical mistakes, which can be difficult to detect, especially when shared with users who may modify the spreadsheets, making it hard to distinguish between original and modified issues.
Innovation Solution
An overlay system is implemented as an add-in for spreadsheet programs, providing an overlay window that displays augmentation data without modifying the original spreadsheet, using an overlay component and engine component to track visible cells and generate overlay elements like background color changes for suspect cells, allowing for auditor reviews and data storage separately from the spreadsheet.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Difficulty of detecting and measuring
If tools modify the spreadsheet to highlight problems (e.g., changing background color of suspect cells to red), then detection of suspect cells is improved, but it becomes extremely difficult to determine whether the cause of a problem is the spreadsheet as provided by the developer or modification to the spreadsheet
Solution Approach 1:
The system segments the visualization function by creating a separate overlay window that displays audit results, rather than modifying the original spreadsheet cells directly. This separation allows the original spreadsheet data to remain unchanged while still providing visual feedback on suspect cells through the overlay interface.
Solution Approach 2:
The patent introduces an overlay window as an intermediary layer between the user and the spreadsheet data. This intermediary displays audit results and suspect cell information without directly modifying the underlying spreadsheet, thus preserving the original data while providing enhanced detection capabilities.
2Adaptability or versatility
If a spreadsheet contains thousands of cells with complex formulas and data dependencies, then the spreadsheet's analytical capability is improved, but it becomes difficult for the developer to ensure that the spreadsheet performs the desired calculations
Solution Approach 1:
The system implements automated auditing tools that analyze spreadsheet formulas and data dependencies, providing feedback to developers about potential errors, circular references, and suspicious patterns. This feedback mechanism helps manage the complexity of large spreadsheets with thousands of cells by automatically identifying issues that would be difficult to detect manually.
3Loss of information
If tools provide static reports of audit results listing cells by identifier, then the audit information is provided, but it is not visually intuitive and requires cross-referencing with cell locations
Solution Approach 1:
The system creates a visual copy or representation of the spreadsheet grid in the overlay window, displaying suspect cell information in a format that mirrors the original spreadsheet layout. This allows users to visually locate suspect cells without needing to cross-reference identifiers with cell positions, significantly improving the ease of operation and visual intuitiveness of the audit results.
Data Source
AI summary
A method and system for providing an overlay window for an application to display augmentation data over the user interface of the application is provided. An overlay system includes an overlay component and an engine component that interface with the application and a presentation manager used by the application to provide the application window. When the overlay system starts up, it initializes the overlay component and the engine component. The overlay component tracks the portion of the application that is currently visible and displays augmentation data in an overlay window over the application window. The engine component provides augmentation functionality that generates the augmentation data and generates the overlay window based on the augmentation data. When the visible portion of the application data changes, the overlay component requests the engine component to generate the overlay window for the currently visible application data.