Data processing apparatus and method for a reformattable multidimensional spreadsheet

• US 5,317,686 A
• Filed: 03/10/1993
• Issued: 05/31/1994
• Est. Priority Date: 09/10/1990
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: Lotus Development Corporation (International Business Machines Corporation)
• Inventors: Kleppner, Paul S., Edelson, Glenn D., Shaver, Robert S., Salas, R. Pito
• Primary Examiner(s): YOUNT, STEVEN R
• Assistant Examiner(s): Breene, John E.
• Application Number:
  US08/033,052
• Time in Patent Office: 447 Days
• Field of Search: 395/154-161, 395/152, 395/148, 395/144, 395/145-147, 395/149-151, 395/153, 395/800, 395/119
• US Class Current: 715/212
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

Spreadsheet apparatus enables reformatting and renaming of items forming the spreadsheet. A series of items forms a dimension along an axis of the spreadsheet. A label icon or labelling entity is user nameable to describe the series of items of an axis. Repositioning of the label icons repositions respective series of items and thus redefines/rearranges the axes of the spreadsheet. Sub-axes to an axis are similarly formed by series of items associated with a respective label icon. Order of label icons in predefined areas of a working screen view determine hierarchy of main axis and sub-axes for the label icons. There is a different predefined area for the possible vertical axes, possible horizontal axes, and the possible orthogonal axes of the spreadsheet. A cell module holds spreadsheet data in a matrix of memory cells. A symbol table translates between current specified names of items in the spreadsheet and indexes to cells of the cell module. Thus, a user is able to rearrange and/or relabel icons in the spreadsheet screen view to reformat the spreadsheet, and the supporting computer members provide display of the spreadsheet rearranged according to position of the icons without losing data of the items as held in respective intersections of the spreadsheet.

Claims

1. In a computer system having a digital processor, input means coupled to the digital processor and a display unit for displaying a multidimensional spreadsheet from the digital processor, data processing apparatus comprising:

a working screen view displayable on the display unit by the digital processor and having a) at least one dimension, each dimension being formed of a respective set of user defined category items that define a user desired category, and b) for each dimension, a corresponding icon for identifying the dimension, depending on screen view position of the corresponding icon each dimension of the working screen view is displayed on an axis selected from a horizontal axis, a vertical axis and an orthogonal axis in the screen view, a user through the input means effecting positioning of the corresponding icon in the screen view, there being a change in axis along which a dimension is displayed with a change in screen view position of the corresponding icon; and

computer means responsive to a user through the input means positioning each icon in the screen view and for displaying in the same screen view on the display unit a dimension along an axis based on position of the corresponding icon in the screen view.

2. Apparatus as claimed in claim 1 wherein each icon has a label which is user definable.

3. Apparatus as claimed in claim 1 wherein each of the user defined category items of a dimension is displayed as a distinct node along the axis along which the dimension is displayed, each node defining a different position along the axis.

4. Apparatus as claimed in claim 1 wherein each item of a dimension is user definable through the input means.

5. Apparatus as claimed in claim 1 wherein the working screen view has a first dimension and a second dimension displayed on parallel axes, the first dimension having a corresponding first icon and the second dimension having a corresponding second icon, user defined category items of the second dimension providing a set of subdivision items for each user defined category item in the first dimension when the second icon is positioned adjacent the first icon in the screen view;

and

the computer means being responsive to a swapping of position of the first icon and the second icon by subdividing each user defined category item in the second dimension associated with the second icon by the user defined category items of the first dimension associated with the first icon.

6. Apparatus as claimed in claim 1 wherein the working screen view further includes, for each axis, a predefined region in which icons are positionable therein;

and

the computer means being responsive to positioning of an icon in a predetermined region by displaying the dimension of the icon along an axis corresponding to the redefined region.

7. Apparatus as claimed in claim 6 wherein the computer means is responsive to an order of adjacent icons in a predefined region such that:

(a) in response to a user positioning one icon leftward another icon in the predefined region corresponding to the vertical axis in the working screen view, the computer means establishes a row extending from the vertical axis for each user defined category item in the dimension of the one icon and subdivides each established row with the user defined category items in the dimension of the other icon;

(b) in response to a user positioning a first icon above a second icon in the predefined region corresponding to the horizontal axis in the working screen view, the computer means establishes a column extending from the horizontal axis for each user defined category item in the dimension of the first icon and subdivides each established column with the user defined category items in the dimension of the second icon; and

(c) in response to a user positioning a desired icon leftward an adjacent icon in the predefined region corresponding to the orthogonal axis in the working screen view, the computer means establishes, for each item in the dimension of the desired icon, a matrix of rows and columns from the vertical and horizontal axes and subdivides each established matrix with the items in the dimension of the adjacent icon.

8. Apparatus as claimed in claim 1 wherein for each user defined category item in a dimension displayed along the horizontal axis, the working screen view provides a row extending from the horizontal axis, and for each user defined category item in a dimension displayed along the vertical axis, the working screen view provides a column extending from the vertical axis;

and

the apparatus further comprising selection means for enabling a user, through the input means, to select one of a whole row and a whole column in the working screen view based on the corresponding icon.

9. Apparatus as claimed in claim 8 wherein said selection means further enables a user, through the input means, to select portions of rows and columns in the working screen view according to corresponding icon.

10. Apparatus as claimed in claim 1 further comprising means for forming and adding a new user defined category item to a user desired dimension displayed along an axis in the working screen view;

and

the computer means being responsive to new user defined category items formed by the forming and adding means by displaying the new items in dimensions along respective axes of the working screen view.

11. Apparatus as claimed in claim 10 wherein the forming and adding means includes a dispenser icon for generating a series of user desired new user defined category items, the dispenser icon enabling the user to interactively define new items.

12. Apparatus as claimed in claim 10 wherein the new items in the working screen view include at least one of a series of number, a series of letters, a series of combinations of numbers and letters, a series of years, a series of quarters, a series of months, a series of abbreviations of months, a series of days of the week, and a series of abbreviations of the days of the week.

13. Apparatus as claimed in claim 1 further comprising means for adding to the working screen view icons for identifying new dimensions, the icons for identifying new dimensions being user positionable through the input means and user redefinable through the input means.

14. Apparatus as claimed in claim 1 wherein the working screen view further includes a calculation portion separate from an area covered by the axes, the calculation portion providing a plurality of user established mathematical expressions, established by the user through the input means, for relating information of certain items of displayed dimensions to information of other items.

15. Apparatus as claimed in claim 4 wherein in the working screen view each user defined category item has a label, and in response to user changing the label of an item in a displayed dimension in the working screen view, the computer means changes each occurrence of the label of the item in the mathematical expressions in the calculation portion of the working screen view.

16. Apparatus as claimed in claim 14 wherein the mathematical expressions include references to user defined category items of displayed dimensions, the references for referencing information of the corresponding items.

17. Apparatus as claimed in claim 14 wherein:

the plurality of mathematical expressions includes working mathematical operations involving information of items of a certain displayed dimension; and

in response to addition of new user defined category items to the certain dimension in the working screen view, the computer means applies the working mathematical operations to information of the new items.

18. Apparatus as claimed in claim 14 wherein the mathematical expressions include a restriction clause and an exclusion clause, the restriction clause specifying application of a mathematical expression to certain items, the exclusion clause specifying application of a mathematical expression to all except certain items;

the computer means being responsive to a restriction clause in a mathematical expression by applying the mathematical expression to only information of certain items, and being responsive to an exclusion clause in a mathematical expression by applying the mathematical expression to information of all except certain items.

19. Apparatus as claimed in claim 14 wherein the calculation portion further includes error checking means for determining and indicating errors in the mathematical expressions.

20. Apparatus as claimed in claim 19 further comprising solution means for displaying in the working screen view solutions to errors indicated by the error checking means and for correcting mathematical expressions upon user selection of an indicated solution through the input means.

21. Apparatus as claimed in claim 14 wherein the calculation portion includes, for each mathematical expression, an indication adjacent the mathematical expression for indicating whether a conflict exists in the mathematical expression.

22. Apparatus as claimed in claim 14 wherein the mathematical expressions include dimension based expressions which define data of an item of a displayed dimension from data of other items of the dimension, each item being indicated with respect to its position in the dimension relative to positions of other items in the dimension.

23. Apparatus as claimed in claim 14 wherein:

a mathematical expression includes a user defined group name to indicate a user desired set of items of a dimension;

the computer means being responsive to a group name in a mathematical expression by applying the mathematical expression to information of each item in the user desired set of items corresponding to the group name.

24. Apparatus as claimed in claim 23 further comprising means for generating a user desired relationship among information of each item indicated by a group name;

and

the computer means being responsive to the processing means by forming a summary item corresponding to the relationship generated by the processing means and displays an indication of the summary item in the working screen view in the dimension of the set of items indicated by the group name.

25. Apparatus as claimed in claim 24 wherein in response to certain user input through the input means, the computer means further alternates display in the working screen view, between (a) the dimension having the set of items together with the summary item and (b) the dimension with the summary item in place of the set of items corresponding to the group name;

the computer means alternating display between (a) and (b) upon each subsequent certain user input through the input means.

26. Apparatus as claimed in claim 24 wherein the relationship generated by the processing means includes either (i) a total, (ii) an average, (iii) a determination of minimum, (iv) a determination of maximum, or (v) a count of and standard deviation of the items corresponding to a group name.

27. Apparatus as claimed in claim 1 further comprising a computer procedure executable by the digital processor for providing a user definable group name to a user selected set of items of a dimension along an axis, the working screen view displaying the group name adjacent the dimension along the axis and the computer means responsive to use of the group name to indicate the set of selected items for which the group name was provided.

28. Apparatus as claimed in claim 1 wherein:

one of plural icons identifying respective dimensions along the orthogonal axis is user selectable; and

the computer means, in response to selection of an icon identifying a dimension along the orthogonal axis, enabling display of a succession of matrixes corresponding to items of the dimension, the succession of matrixes being displayed in the working screen view one matrix at a time upon user command.

29. Apparatus as claimed in claim 28 wherein the working screen view further includes an indication of position of the item corresponding to a displayed matrix relative to a last item in the dimension.

30. Apparatus as claimed in claim 28 wherein the working screen view further includes graphical elements for displaying a next and a previous matrix in the succession of matrices;

items of the dimension having an order of first through last; and

during display of the matrix corresponding to the last item of the dimension, the graphical elements providing an indication that the matrix corresponds to the last item of the dimension and is the last displayable matrix of the succession of matrices.

31. In a computer system having a digital processor, input means coupled to the digital processor and a display unit for displaying a multidimensional spreadsheet from the digital processor, data processing apparatus comprising:

a working screen view displayed on the display unit and having multiple axes and a plurality of regions, each region defining a reference position from which location in the screen view is determined, there being a different reference position for each axis of the screen view such that each reference position corresponds to one axis;

a plurality of user defined indicators in the working screen view, each indicator being displayed in the working screen view and providing an indication of a series of items of a desired category, position of the indicators in the working screen view being changed by a user through the input means such that the indicators are user repositionable in the working screen view; and

computer means responsive to positioning of the indicators in the working screen view with respect to the reference positions in the working screen view such that, in response to a user positioning an indicator in one of the reference positions in the working screen view, the computer means displays in the same working screen view the series of items indicated by the indicator along the axis corresponding to the reference position, different items in the series defining different positions on the axis, the series of items being displayed along a different axis in response to user changing position of the corresponding indicator to another of the reference positions.

32. Apparatus as claimed in claim 31 wherein:

the reference positions include (i) a first predefined area for a horizontal axis of the working screen view, (ii) a second predefined area for a vertical axis of the working screen view, and (iii) a third predefined area for an orthogonal axis of the working screen view; and

(a) in response to a user positioning a desired indicator in the first predefined area, the computer means displays the series of items corresponding to the indicator along the horizontal axis;

(b) in response to a user positioning a desired indicator in the second predefined area, the computer means displays the series of items corresponding to the indicator along the vertical axis; and

(c) in response to a user positioning a desired indicator in the third predefined area in the working screen view, the computer means forms from the series of items corresponding to the desired indicator a series of positions along the orthogonal axis, each item of the series forming a different position on the orthogonal axis, and for each position on the orthogonal axis there being a matrix of intersecting rows and columns, each matrix having (i) a horizontal axis formed of a series of items corresponding to an indicator positioned in the first predefined area in the working screen view, different items providing different positions on the horizontal axis from which columns of the matrix extend, and (ii) a vertical axis formed of a series of items corresponding to another indicator positioned in the second predefined area in the working screen view, different items providing different positions along the vertical axis from which rows of the matrix extend, each matrix for holding certain information of the corresponding item.

33. Apparatus as claimed in claim 31 wherein in response to a user positioning in a desired order a plurality of indicators in one of the reference positions in the working screen view, the ordered plurality having a first indicator followed by at least a second indicator, such that a sequence of indicators is established in the reference position, the computer means (a) displays in the working screen view a series of items corresponding to the first indicator along the axis corresponding to the reference position, each item in the series being a different position on the axis and (b) displays in the working screen view a related axis for each of other said indicators in said sequence, each related axis having positions defined by the different items in the series of items corresponding to the respective indicator, such that for each position on an axis formed from a series of items of a previous indicator in said sequence, the computer means forms a sub-axis to the axis, from the series of items of a succeeding indicator, each item in the series of items of the succeeding indicator defining a different position on the sub-axis.

34. Apparatus as claimed in claim 31 wherein the computer means includes:

a cell module for providing association between user desired information and positions along axes formed from indicators positioned in the reference positions in the working screen view;

a symbol table for translating by cross-referencing user specified names of items as displayed in the working screen view to positions along axis formed from indicators positioned in the reference positions in the working screen view, names of items being changed as desired; and

display defining means for updating the working screen view in response to a user changing names of items and repositioning indicators with respect to the reference positions in the working screen view, the display defining means being coupled to the symbol table to obtain therefrom axis positions of user specified items names, and coupled to the cell module to obtain therefrom user desired information to be displayed at the axis position obtained from the symbol table, such that an updated working screen view retains user desired information associated with respective positions along axes and displays changed items names and changed axes along which series of items are displayed in response to positions of corresponding indicators with respect to the reference positions in the working screen view.

35. In a computer system, a method of displaying a multidimensional spreadsheet comprising the steps of:

providing a plurality of user defined indicators in a working screen view, each indicator for indicating a series of category items displayable along an axis in the working screen view, axis along which a series of category items is displayed depending on position of the indicator in the working screen view, the series of items being displayed along a different axis in response to change of position of the corresponding indicator in the working screen view, and each item having a name which is user changeable;

storing in memory user desired information for items of different indicators in the working screen view, information of each item being stored in the memory according to memory index; and

in response to user positioning of indicators in the working screen view;

(a) cross-referencing names of items displayed in the working screen view to respective memory index for accessing from memory corresponding information of said items;

(b) accessing from memory, information for items of indicators in the working screen view; and

(c) displaying in the same working screen view (i) a series of items along respective axes according to position of indicators in the working screen view, and (ii) information corresponding to the items in areas extending from respective axes according to position of items along respective axes.

36. A method as claimed in claim 35 wherein position of each indicator is with respect to predefined areas of the working screen view, there being a different predefined area for each axis of the screen view such that each predefined area corresponds to a different axis, and a series of items corresponding to an indicator is displayed along an axis as a function of position of the indicator with respect to a predefined area corresponding to the axis.

37. A method as claimed in claim 35 wherein there are up to 12 axes along which series of items are displayable in the working screen view.

38. A method as claimed in claim 37 wherein the axes include subaxes of a horizontal axis, vertical axis and orthogonal axis.