Method and implementation for referencing of dynamic data within spreadsheet formulas

• US 20060069696A1
• Filed: 09/30/2004
• Published: 03/30/2006
• Est. Priority Date: 09/30/2004
• Status: Active Grant

• 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.
• Granted Patent: US 7,415,481 B2
• Time in Patent Office: Days
• 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 for referring to a set of data, 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; and

associating the semantic designation with the structural reference.

2. A 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. A 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. A method according to claim 1, wherein the semantic designation describes one or more items of metadata.

5. A 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. A 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. A method according to claim 1, wherein the data structure is a structure element of a table identified by the semantic designation.

8. A 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. A method according to claim 1, wherein the structural reference is an unbounded number of cells.

10. A 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.

11. A method according to claim 10, 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.

12. A 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.

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

14. A method according to claim 13, wherein the application formula is provided to the user that one or more arguments that include a collection of the one or more semantic designations used to establish the structural reference.

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

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

18. A computer readable medium according to claim 15, 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.

19. A computer readable medium according to claim 15, 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.

20. A computer readable medium according to claim 15, wherein the semantic designation describes one or more items of metadata.

21. A computer readable medium according to claim 15, wherein the structural reference refers to a location of the data structure of the one or more items of data.

22. A computer readable medium according to claim 19, wherein the location of the data structure is a position of a column, a row, or one or more cells.

23. A computer readable medium according to claim 15, wherein the data structure is a structure element of a table identified by the semantic designation.

24. A computer readable medium according to claim 21, 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.

25. A computer readable medium according to claim 15, wherein the structural reference is an unbounded number of cells.

26. A computer readable medium according to claim 15, 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.

27. A computer readable medium according to claim 24, 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.

28. A computer readable medium according to claim 15, 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.

29. A computer readable medium according to claim 15, wherein the structural reference is included in an application formula.

30. A computer readable medium according to claim 27, wherein the application formula is provided to the user that one or more arguments that include a collection of the one or more semantic designations used to establish the structural reference.

34. A method according to claim 29, 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.

35. A method according to claim 29, further comprising:

receiving one or more changes to one or more items of data referred to by the structural reference;

changing the structural reference to reflect the change to the data; and

displaying the changed structural reference with the collection of the one or more semantic designation.

17. A computer-readable medium having computer-executable instructions for performing steps 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; and

associating the semantic designation with the structural reference.

31. A computer readable medium according to claim 17, wherein the structural reference is not created if one or more of the semantic designations is ambiguous.

32. A computer readable medium according to claim 31, wherein the semantic designation is ambiguous if the semantic designation refers to two or more data structures.

33. A method 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 the user that describe 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.