Method and system for formulaically redirecting and modifying formula set operations in a spreadsheet application

• US 7,444,584 B1
• Filed: 03/30/2004
• Issued: 10/28/2008
• Est. Priority Date: 03/30/2004
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Hobbs, Craig A.
• Primary Examiner(s): Hutton; Doug
• Assistant Examiner(s): Nguyen; Maikhanh
• Application Number:
  US10/813,418
• Time in Patent Office: 1,673 Days
• Field of Search: 715/503, 715/504, 715/530, 715/212, 715/217, 715/219, 715/220, 715/255, 715/267
• US Class Current: 715/212
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

Methods and systems are provided for formulaically redirecting and modifying formula set operations in a spreadsheet application. A spreadsheet application may link the contents of one spreadsheet cell to other spreadsheet cells so that changes in one cell are reflected in linked cells. Three spreadsheet functions are provided which allow for formulaically redirecting and modifying formula set operations in a spreadsheet application. A first spreadsheet function, SETATREF, redirects a formula set in one spreadsheet cell to another spreadsheet cell. The SETATREF function may also allow a formula set to one spreadsheet cell to be redirected to and modified in a second spreadsheet cell. A second spreadsheet function, SETATREFEXPR, redirects a formula set to a formulaic location within a spreadsheet cell. A third function, SETATREFEVAL, allows for an evaluation of a formula at formula set-time.

Claims

1. A method of formulaically specifying the redirection or modification of a formula in a spreadsheet application at formula set-time, comprising:

setting a spreadsheet formula to a first spreadsheet cell;

in response to setting the spreadsheet formula to the first spreadsheet cell, obtaining a formula previously set to the first spreadsheet cell, wherein prior to obtaining the formula previously set to the first spreadsheet cell, parsing the spreadsheet formula set to the first spreadsheet cell to determine any required evaluation or recalculation caused by the spreadsheet formula set to the first spreadsheet cell, wherein the spreadsheet formula set to the first spreadsheet cell comprises a value to be evaluated by the formula previously set to the first spreadsheet cell;

identifying a cell reference to a second spreadsheet cell in at least one member of a group comprising;

the spreadsheet formula set to the first spreadsheet cell and the spreadsheet formula previously set to the first spreadsheet cell;

in response to setting the spreadsheet formula to the first spreadsheet cell, determining whether data associated with the first spreadsheet cell should be redirected to the second spreadsheet cell identified by the cell reference;

redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for entry into the second spreadsheet cell when data associated with the first spreadsheet cell is redirected to the second spreadsheet cell identified by the cell reference, wherein redirecting data associated with the first spreadsheet cell to the second spreadsheet cell includes redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for evaluation by a spreadsheet formula set to the second spreadsheet cell and redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for modifying the spreadsheet formula set to the second spreadsheet cell;

determining whether the spreadsheet formula previously set to the first spreadsheet cell requires evaluation in response to the spreadsheet formula set to the first spreadsheet cell when the data is evaluated by the spreadsheet formula set to the second spreadsheet cell;

evaluating the spreadsheet formula previously set to the first spreadsheet cell in response to the spreadsheet formula set to the first spreadsheet cell when the data is evaluated by the spreadsheet formula set to the second spreadsheet cell; and

passing the evaluated result of evaluating the spreadsheet formula previously set to the first spreadsheet cell for evaluation by the spreadsheet formula set to the second spreadsheet cell.

2. The method of claim 1, further comprising determining the cell reference for the second spreadsheet cell from the spreadsheet formula set to the first spreadsheet cell.

3. The method of claim 2, whereby if no cell reference for the second spreadsheet cell is determined from the spreadsheet formula set to the first spreadsheet cell, determining the cell reference for the second spreadsheet cell from the spreadsheet formula previously set to the first spreadsheet cell.

4. The method of claim 1, further comprising passing the evaluated result of evaluating the spreadsheet formula previously set to the first spreadsheet cell for modifying a spreadsheet formula set to the second spreadsheet cell.

5. A computer-readable storage medium having computer-executable instructions for formulaically specifying the redirection or modification of a formula in a spreadsheet application at formula set-time, comprising:

setting a spreadsheet formula to a first spreadsheet cell;

in response to setting the spreadsheet formula to the first spreadsheet cell, obtaining a formula previously set to the first spreadsheet cell, wherein prior to obtaining the formula previously set to the first spreadsheet cell, parsing the spreadsheet formula set to the first spreadsheet cell to determine any required evaluation or recalculation caused by the spreadsheet formula set to the first spreadsheet cell, wherein the spreadsheet formula set to the first spreadsheet cell comprises a value to be evaluated by the formula previously set to the first spreadsheet cell;

identifying a cell reference to a second spreadsheet cell in at least one member of a group comprising;

the spreadsheet formula set to the first spreadsheet cell and the spreadsheet formula previously set to the first spreadsheet cell;

in response to setting the spreadsheet formula to the first spreadsheet cell, determining whether data associated with the first spreadsheet cell should be redirected to the second spreadsheet cell identified by the cell reference;

redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for entry into the second spreadsheet cell when data associated with the first spreadsheet cell is redirected to the second spreadsheet cell identified by the cell reference, wherein redirecting data associated with the first spreadsheet cell to the second spreadsheet cell includes redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for evaluation by a spreadsheet formula set to the second spreadsheet cell and redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for modifying the spreadsheet formula set to the second spreadsheet cell;

determining whether the spreadsheet formula previously set to the first spreadsheet cell requires evaluation in response to the spreadsheet formula set to the first spreadsheet cell when the data is evaluated by the spreadsheet formula set to the second spreadsheet cell;

evaluating the spreadsheet formula previously set to the first spreadsheet cell in response to the spreadsheet formula set to the first spreadsheet cell when the data is evaluated by the spreadsheet formula set to the second spreadsheet cell; and

passing the evaluated result of evaluating the spreadsheet formula previously set to the first spreadsheet cell for evaluation by the spreadsheet formula set to the second spreadsheet cell.

6. The computer-readable storage medium of claim 5, further comprising determining the cell reference for the second spreadsheet cell from the spreadsheet formula set to the first spreadsheet cell.

7. The computer-readable storage medium of claim 6, whereby if no cell reference for the second spreadsheet cell is determined from the spreadsheet formula set to the first spreadsheet cell, determining the cell reference for the second spreadsheet cell from the spreadsheet formula previously set to the first spreadsheet cell.

8. A system for formulaically specifying the redirection or modification of a formula in a spreadsheet application at formula set-time, comprising:

a processor; and

a memory having computer-executable instructions stored thereon, wherein the computer-executable instructions include;

setting a spreadsheet formula to a first spreadsheet cell;

in response to setting the spreadsheet formula to the first spreadsheet cell, obtaining a formula previously set to the first spreadsheet cell, wherein prior to obtaining the formula previously set to the first spreadsheet cell, parsing the spreadsheet formula set to the first spreadsheet cell to determine any required evaluation or recalculation caused by the spreadsheet formula set to the first spreadsheet cell, wherein the spreadsheet formula set to the first spreadsheet cell comprises a value to be evaluated by the formula previously set to the first spreadsheet cell;

identifying a cell reference to a second spreadsheet cell in at least one member of a group comprising;

the spreadsheet formula set to the first spreadsheet cell and the spreadsheet formula previously set to the first spreadsheet cell;

in response to setting the spreadsheet formula to the first spreadsheet cell, determining whether data associated with the first spreadsheet cell should be redirected to the second spreadsheet cell identified by the cell reference; and

redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for entry into the second spreadsheet cell when data associated with the first spreadsheet cell is redirected to the second spreadsheet cell identified by the cell reference, wherein redirecting data associated with the first spreadsheet cell to the second spreadsheet cell includes redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for evaluation by a spreadsheet formula set to the second spreadsheet cell and redirecting data associated with the first spreadsheet cell to the second spreadsheet cell for modifying the spreadsheet formula set to the second spreadsheet cell;

determining whether the spreadsheet formula previously set to the first spreadsheet cell requires evaluation in response to the spreadsheet formula set to the first spreadsheet cell when the data is evaluated by the spreadsheet formula set to the second spreadsheet cell;

evaluating the spreadsheet formula previously set to the first spreadsheet cell in response to the spreadsheet formula set to the first spreadsheet cell when the data is evaluated by the spreadsheet formula set to the second spreadsheet cell; and

passing the evaluated result of evaluating the spreadsheet formula previously set to the first spreadsheet cell for evaluation by the spreadsheet formula set to the second spreadsheet cell.

9. The system of claim 8, further comprising determining the cell reference for the second spreadsheet cell from the spreadsheet formula set to the first spreadsheet cell.

10. The system of claim 9, whereby if no cell reference for the second spreadsheet cell is determined from the spreadsheet formula set to the first spreadsheet cell, determining the cell reference for the second spreadsheet cell from the spreadsheet formula previously set to the first spreadsheet cell.