Method and implementation for referencing of dynamic data within spreadsheet formulas

• US 7,415,481 B2
• Filed: 09/30/2004
• Issued: 08/19/2008
• Est. Priority Date: 09/30/2004
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Collie, Robert C., Ellis, Charles D., Androski, Matthew J., Zhong, Weide, Niemisto, Juha P., Chirilov, Joseph M., Becker, Andrew J., Peyton-Jones, Simon, Hawking, Robert G.
• Primary Examiner(s): Breene; John
• Assistant Examiner(s): Bullock; Joshua
• Application Number:
  US10/956,940
• Publication Number:
  US 20060069696A1
• Time in Patent Office: 1,419 Days
• Field of Search: 707/102, 715/504, 715/212
• US Class Current: 1/1
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

The present invention relates to a new type of structured name within a spreadsheet program that allows users to refer to tables, columns of data within tables, headers of tables, totals from tables, PivotTables®, and portions of PivotTable® data by the data'"'"'s headers. Embodiments of the present invention comprise a method for referring to a set of data that comprises receiving one or more semantic designations, wherein the one or more semantic designations name a set of context information that describes one or more items of data in a data structure, automatically creating a structural reference, and associating the context information with the structural reference. Other embodiments of the present invention relate to an interaction with a user interface for displaying on a display device a structural reference to data in a spreadsheet that comprises receiving one or more semantic designations from the user that refer to one or more elements of the data structure, creating a structural reference to the elements of the data structure, and displaying the structural reference as a collection of the one or more semantic designations.

Claims

1. A method, executed in a computer system, for referring to a set of data within a spreadsheet application, comprising:

receiving one or more semantic designations, wherein the one or more semantic designations describe one or more items of data in a data structure;

automatically creating a structural reference to the data structure, wherein the structural reference is an unbounded number of cells;

associating the one or more semantic designations with the structural reference, wherein associating the one or more semantic designations with the structural reference comprises at least one of the following;

producing a set of context information from a user input semantic designation including a specified syntax, and

employing the structural reference in at least one formula, wherein the spreadsheet application automatically maps the structural reference to the data structure;

determining when context information in the spreadsheet is proximate the data;

when context information is proximate the data, providing a context for evaluating which cells contain the data and which cells contain a data label and updating the structural reference wherein the label and data remained linked;

when the data is stored in a database, determining ambiguity within the data, wherein the ambiguity arises because the one or more semantic designations contain duplicate captions;

when ambiguity within the data is due to the one or more semantic designations containing duplicate captions, disambiguating the one or more semantic designations by use of a modifier, thereby creating a modified semantic designation;

storing the association between the one or more semantic designations or the modified semantic designation and the structural reference.

2. The method according to claim 1, wherein the semantic designations describe one of a list, a table, a field, a member, a column, a row, one or more cells, or a dimension.

3. The method according to claim 1, wherein the semantic designations includes one or more of a list name, a table name, a row name, a column name, a cell name, a row location, a cell location, a column location, or a data detail.

4. The method according to claim 1, wherein the semantic designation describes one or more items of metadata.

5. The method according to claim 1, wherein the structural reference refers to a location of the data structure of the one or more items of data.

6. The method according to claim 5, wherein the location of the data structure is a position of a column, a row, or one or more cells.

7. The method according to claim 1, wherein the data structure is a structure element of a table identified by the semantic designation.

8. The method according to claim 7, wherein the type of structure element includes one or more of a list, a table, a field, a member, a column, a row, one or more cells, or a dimension.

9. The method according to claim 1, further comprising:

receiving a change to the data included in one or more structural references; and

automatically adjusting the one or more structural reference to maintain the reference to one or more items of pre-existing data.

10. The method according to claim 9, comprising automatically adjusting the one or more structural references to include added data that is part of the data structure described by the one or more semantic designations used to establish the one or more structural references.

11. The method according to claim 1, wherein the structural reference is provided to the user as a collection of the one or more semantic designations used to establish the structural reference.

12. The method according to claim 1, wherein the structural reference is included in an application formula.

13. The method according to claim 12, wherein the application formula is provided to the user having one or more arguments, wherein the one or more arguments include the one or more semantic designations used to establish the structural reference.

14. The method according to claim 1, wherein the structural reference is not created if one or more of the semantic designations is ambiguous.

15. The method according to claim 14, wherein the semantic designation is ambiguous if the semantic designation refers to two or more data structures.

16. A computer storage medium having computer-executable instructions stored thereon, the computer-executable instructions for performing a computer-executable method for referring to a set of data within a spreadsheet application, the method comprising:

receiving an application formula as input into the spreadsheet application, wherein the application formula includes one or more arguments;

receiving one or more semantic designations, wherein the one or more semantic designations describe one or more items of data in a first data structure, the one or more arguments of the application formula include the one or more semantic designations;

automatically creating a structural reference to the data structure;

associating the one or more semantic designations with the structural reference, wherein associating the one or more semantic designations with the structural reference comprises at least one of the following;

producing a set of context information from a user input semantic designation including a specified syntax, and

employing the structural reference in at least one formula, wherein the spreadsheet application automatically maps the structural reference to the data structure;

determining when context information in the spreadsheet is proximate the data;

when context information is proximate the data, providing a context for evaluating which cells contain the data and which cells contain a data label and updating the structural reference wherein the label and data remained linked;

when the data is stored in a database, determining ambiguity within the data, wherein the ambiguity arises because the one or more semantic designations contain duplicate captions;

when ambiguity within the data is due to the one or more semantic designations containing duplicate captions, disambiguating the one or more semantic designations by use of a modifier, thereby creating a modified semantic designation;

storing the association between the one or more semantic designations or the modified semantic designation and the structural reference;

receiving one or more changes to the one or more items of data;

determining a new data structure referred to by the one or more semantic designations, wherein the new data structure includes the one or more items of data and the one or more changes to the one or more items of data;

in response to determining a new data structure, automatically creating a new structural reference associated with the one or more semantic designations wherein the new structural reference includes the one or more items of data and the one or more changes to the one or more items of data in the new data structure retrieving one or more items of data from the new data structure referred to by the new structural reference;

executing the application formula with one or more items of data from the new data structure to create a result; and

displaying the result.

17. The computer storage medium according to claim 16, wherein the semantic designations describe one of a list, a table, a field, a member, a column, a row, one or more cells, or a dimension.

18. The computer storage medium according to claim 16, wherein the semantic designations includes one or more of a list name, a table name, a row name, a column name, a cell name, a row location, a cell location, a column location, or a data detail.

19. The computer storage medium according to claim 16, wherein the semantic designation describes one or more items of metadata.

20. The computer storage medium according to claim 16, wherein the structural reference refers to a location of the data structure of the one or more items of data.

21. The computer storage medium according to claim 20, wherein the location of the data structure is a position of a column, a row, or one or more cells.

22. The computer storage medium according to claim 16, wherein the data structure is a structure element of a table identified by the semantic designation.

23. The computer storage medium according to claim 22, wherein the type of structure element includes one or more of a list, a table, a field, a member, a column, a row, one or more cells, or a dimension.

24. The computer storage medium according to claim 16, wherein the structural reference is an unbounded number of cells.

25. The computer storage medium according to claim 16, further comprising:

receiving a change to the data included in one or more structural references; and

automatically adjusting the one or more structural reference to maintain the reference to one or more items of pre-existing data.

26. The computer storage medium according to claim 25, comprising automatically adjusting the one or more structural references to include added data that is part of the data structure described by the one or more semantic designations used to establish the one or more structural references.

27. The computer storage medium according to claim 16, wherein the structural reference is provided to the user as a collection of the one or more semantic designations used to establish the structural reference.

28. The computer storage medium according to claim 16, wherein the structural reference is not created if one or more of the semantic designations is ambiguous.

29. The computer storage medium according to claim 28, wherein the semantic designation is ambiguous if the semantic designation refers to two or more data structures.

30. A method executable in a computer system for displaying, on a display device, a reference to data in a spreadsheet, the method comprising:

receiving one or more semantic designations from a user that describe one or more elements of a data structure;

creating a structural reference, associated with the one or more semantic designations, to the elements of the data structure, wherein creating the structural reference to the elements of the data structure comprises at least one of the following;

producing a set of context information from a user input semantic designation including a particular syntax, and

employing the structural reference in at least one formula, wherein the spreadsheet application automatically maps the structural reference to the data structure;

determining when context information in the spreadsheet is proximate the data;

when context information is proximate the data, providing a context for evaluating which cells contain the data and which cells contain a data label and updating the structural reference wherein the label and data remained linked;

when the data is stored in a database, determining ambiguity within the data, wherein the ambiguity arises because the one or more semantic designations contain duplicate captions;

when ambiguity within the data is due to the one or more semantic designations containing duplicate captions, disambiguating the one or more semantic designations by use of a modifier, thereby creating a modified semantic designation;

displaying, on the display device, the structural reference as a collection of the one or more semantic designations or the modified semantic designation;

receiving one or more changes to the one or more elements of the data structure;

creating a new structural reference; and

displaying on the display device, the new structural reference as the collection of the one or more semantic designations.

31. The method according to claim 30, further comprising:

receiving an operation that employs the structural reference; and

displaying the operation with the collection of the one or more semantic designations referring to the structural reference, wherein the one or more semantic designations form a collection of arguments within the operation;

displaying a result for the operation; and

in response to creating the new structural reference, displaying a new result for the operation; and

in response to creating the new structural reference, displaying the operation with the collection of the one or more semantic designations referring to the new structural reference.