System and methods for improved scenario management in an electronic spreadsheet

• US 6,438,565 B1
• Filed: 02/07/1996
• Issued: 08/20/2002
• Est. Priority Date: 03/11/1993
• Status: Expired due to Fees

• Current Assignee: Borland Software Corporation (Open Text Corporation)
• Original Assignee: Borland Software Corporation (Open Text Corporation)
• Inventors: Peacock, Gavin, Ammirato, Joseph Michael
• Primary Examiner(s): BREENE, JOHN E
• Application Number:
  US08/597,794
• Time in Patent Office: 2,386 Days
• Field of Search: 395/761-769
• US Class Current: 715/204
• CPC Class Codes: G06F 3/0489 using dedicated keyboard keys or combinations thereof
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 40/197 Version control for software G06F8/71

Abstract

An electronic spreadsheet of the present invention includes a scenario manager having a preferred interface and methods for creating and managing various versions or “scenarios” of a spreadsheet model. Methods are provided for specifying an area of the model to track (capture area) and capturing various versions (modifications) of the base model. Additional methods are provided for automatically identifying and reporting information cells which are affected (“changing” and “result” cells) from version to version. Preferred storage methodology is also described for maintaining multiple versions of a spreadsheet or data model.

Claims

1. In an electronic spreadsheet system for modeling user-specified information in a data model comprising a plurality of information cells, a method for automatically tracking different versions of the data model and reporting differences between versions to a user, the method comprising:

(a) specifying a base set of information cells for the system to track changes;

(b) creating a new version of the data model by modifying at least one information cell from the specified base set;

(c) automatically determining by the system cells of the data model which have changed by comparing cells in the new version against corresponding ones in the base set; and

(d) reporting said cells of the data model which have changed by creating a report, said system determining and listing in the report for each cell which has changed in step (b);

(i) values for said each cell before and after the cell was changed, and

(ii) values for any other cells storing spreadsheet formulas which express new values as a result of said each cell changing.

2. The method of claim 1, wherein step (a) comprises:

selecting a capture area comprising information cells which the system is to automatically track; and

capturing as the base set information in the capture area which the user desires to serve as a reference against which new versions are compared.

3. The method of claim 2, wherein said selecting a capture area includes selecting one of a spreadsheet cell block, a two-dimensional matrix of cells (page), and a three-dimensional matrix of cells (notebook).

4. The method of claim 2, wherein said selecting a capture area includes determining a set of information cells selected to include cells which are to be modified by a user.

5. The method of claim 1, wherein step (b) includes entering new values into at least one of the information cells from the base set.

6. The method of claim 1, wherein step (c) includes:

comparing non-formula information cells in the new version against corresponding ones in the base set, whereby ones of the new version which have changed in value are identified as changing cells; and

comparing formula information cells in the new version against corresponding ones in the base set, whereby ones of the new version which have changed in value are identified as result cells.

7. The method of claim 1, wherein step (c) includes:

(i) comparing non-formula information cells in the new version against corresponding ones in the base set, whereby ones of the new version which have changed in value are identified as changing cells; and

(ii) of the information cells which are not changing cells, determining ones which change value as a result of the changes in the changing cells, whereby ones which have changed value are identified as result cells.

8. The method of claim 7, wherein each said result cell includes a direct or an indirect reference to at least one changing cell.

9. The method of claim 7, wherein the determination of result cells in substep (ii) is limited to examining only to cells which store formulas.

10. The method of claim 7, further comprising:

indicating said changing cells and said result cells to the user.

11. The method of claim 10, wherein said indicating step includes:

displaying the changing cells with a first screen highlight; and

displaying said result cells with a second screen highlight.

12. The method of claim 11, further comprising:

selectively disabling screen highlighting from desired ones of said changing and result cells.

13. The method of claim 1, further comprising:

(d) capturing the new version as a user-nameable scenario.

14. The method of claim 13, further comprising:

(e) repeating steps (b)-(d), whereby a plurality of scenarios is captured.

15. The method of claim 14, further comprising:

(f) storing the base set together with the plurality of captured scenarios on a storage device.

16. The method of claim 15, wherein step (f) includes:

storing the information cells from the base set of the data model as a plurality of cell records; and

for each new version created by the user, only storing records for the determined changing cells.

17. The method of claim 16, wherein each said cell record stores cell information, cell position, and cell attributes.

18. The method of claim 16, where each said record stored for a determined changing cell stores new cell information, cell position, cell attributes, and a version identifier.

19. A system for managing different versions of a data model and reporting differences between versions to a user, the system comprising:

an electronic spreadsheet for expressing the data model as a plurality of interrelated information cells;

means for specifying a reference set of said information cells which serves as a base case for the data model;

means for creating a new version of the data model by modifying at least one of said information cells in the reference set;

comparison means for automatically determining by the system which ones of the information cells in the new version have changed from the reference set; and

means for creating a report listing to the user said cells of the data model which have changed as a result of creation of said new version, said report listing for each cell which has changed

(i) values for said each cell before and after the cell was changed, and

(ii) values for any other cells storing spreadsheet formulas which express new values as a result of said each cell changing.

20. The system of claim 19, wherein said means for specifying a reference set includes:

input device means for entering a selected one of a block of cells, a spreadsheet of cells, and a plurality of spreadsheets.

21. The system of claim 19, wherein said means for creating a new version includes:

input means for entering new values into at least one of the information cells.

22. The system of claim 19, wherein said comparison means includes:

means for comparing each cell in the new version against a corresponding one in the reference set, whereby a non-formula cell having a changed value is identified as a changing cell.

23. The system of claim 22, wherein said comparison means further includes:

means for determining which ones of the information cells change in response to changes in said identified changing cells.

24. The system of claim 23, further comprising:

display means for highlighting changing and result cells.

25. The system of claim 19, further comprising:

input means for entering a user-specified name for each new version.

26. In an electronic spreadsheet system, a method for storing different versions of a spreadsheet model and reporting differences between versions to a user, the method comprising:

(a) maintaining a base version of the spreadsheet model as ordered information on a storage device;

(b) for each new version of the spreadsheet model;

(i) automatically determining by the system portions of the new version which have changed when compared against the base version,

(ii) maintaining the new version by storing additional information for only those portions determined to have changed; and

(iii) reporting to the user said portions of the new version which have changed, wherein portions of the new version which have been directly edited by a user are identified as changing cells, and wherein remaining portions of the new version which have changed in value without direct editing by a user are identified as result cells.

27. The method of claim 26, wherein said spreadsheet model maintains information in a plurality of information cells, wherein said ordered information includes a first set of data records describing information cells of the base version, and wherein said additional information includes a second set of data records describing information cells for only those portions of the new version determined to have changed.

28. The method of claim 27, wherein each data record of the second set which describes an information cell is a version record storing a changed value for its cell.

29. The method of claim 28, wherein each version record further includes a cell location.

30. The method of claim 28, wherein each version record further includes an identifier associating the record with a specific new version.

31. The method of claim 28, wherein version records for a new version are stored so that a specific new version may be inferred.

32. The method of claim 31, wherein version records for a particular new version are grouped as a discrete subset so that the version may be inferred from the ordinal position of the subgroup relative to other subgroups.

33. The method of claim 27, further comprising:

(d) repeating step (b) for multiple new versions; and

(e) storing additional information for each new version as a subsequent subset of said second set of data records.

34. The method of claim 26, wherein said spreadsheet model maintains information in a plurality of information cells, and wherein the substep (i) includes comparing each information cell of the new version against a corresponding one of the base version, whereby a difference in value between the two cells is identified as a change.