Abstract:
This system is a way of building structured spreadsheets using named nested rectangular block of cells to form a hierarchy. This system allows cells to be referenced by non unique names by using the relative location in the hierarchy of the referenced cell with respect to the cell containing the reference. Clones of a section of the hierarchy can be constructed. The clones are permanently linked to the section of the hierarchy from which they were cloned. Future changes to that section of the hierarchy can be propagated to the clones.

Description:
BACKGROUND OF THE INVENTION  
       [0001]     Spreadsheets are very useful but very prone to errors. This is really a direct consequence of the way spreadsheet applications allow users to build spreadsheets. Although, there are methodologies that users can follow which will help them to build more reliable spreadsheets, it is very likely that spreadsheets will continue to be built without using these methodologies as it is unreasonable to expect that average user will be conversant with these methodologies. It may be better if spreadsheet users were given a new easy way of building spreadsheets that naturally forces the spreadsheets to be built in a way that is conducive to preventing many of the errors from occurring. The basic technology for building spreadsheets has not changed for a very long time. In the meantime computers have become vastly more powerful. These vastly more powerful computers makes it is possible to consider innovative new ways of building spreadsheets that were impractical on the earlier generations of computers.  
       BRIEF SUMMARY OF THE INVENTION  
       [0002]     The present invention provides the user with system of building structured spreadsheets that automatically eliminates many of the errors that would be caused by incorrect cell references in formulas and by incorrect replication of cells. Structuring the spreadsheet allows the present invention to provide alternatives to three basic spreadsheet features: relative references, the SUM spreadsheet function and the copy and paste operation. Although, much of the power of spreadsheets is derived from using these three features, using these three features to build spreadsheets also make it easy to produce spreadsheets with errors. The present invention provides alternatives to these three features that automatically make it much harder to produce spreadsheets with errors. The present invention supports localized references, an alternative to relative references, the SUMNAMED spreadsheet function, an alternative to the SUM spreadsheet function and the clone operation, an alternative to the copy and paste operation. These new capabilities are made possible by structuring the cells on a spreadsheet into regions bound to symbolic names. Localized references are used to reference cells by their symbolic name and their containing regions. The SUMNAMED spreadsheet function selectively sums the values of some of the cells in a specified region using the symbolic names of the cells to select the cells to sum. Cloning creates a clone of a progenitor region. The clone and the progenitor are permanently linked so that the cells of the cloned region can be synchronized with the cells of the progenitor after the progenitor has been edited. 
     
    
     BRIEF DESCRIPTION OF THE SEVERAL VIEWS OF THE DRAWING  
       [0003]      FIG. 1  is a screen shot of a spreadsheet illustrating two examples of localized referencing.  
         [0004]      FIG. 2  is a screen shot of a spreadsheet containing a clone and its progenitor before the progenitor has been edited.  
         [0005]      FIG. 3  is a screenshot of the spreadsheet of  FIG. 2  after the progenitor has been edited and the clone synchronized with its progenitor.  
         [0006]      FIG. 4  is a screenshot of a spreadsheet illustrating the use of the SUMNAMED function.  
         [0007]      FIG. 5  is a screenshot of a spreadsheet illustrating the use of planes. 
     
    
     DETAILED DESCRIPTION OF THE INVENTION  
       [0008]     This system uses regions to structure the cells on a spreadsheet. A region is a rectangular block of cells bound to a symbolic name. A region may completely contain another region or be completely contained in another region but a region may not partially overlap another region. Therefore with respect to containment the regions of a spreadsheet form a tree hierarchy where the region consisting all cells of the spreadsheet is the root of the tree hierarchy. A single cell may be a region. A container is a region of more than one cell. A region that is contained in a container is said to be a member of that container. Multiple regions may have the same symbolic name. A symbolic name in this system is not intended to uniquely identify a region but rather, a symbolic name really specifies the type of data contained in a region. This slight paradigm shift will make names much more useful in formulas as we will soon see. The regions should be chosen to correspond to natural hierarchy of the underlying data in the regions of the spreadsheet. For example, the regions may organize the cells of the spreadsheet geographically by city, state and country.  
         [0009]     A formula in a cell may be able reference another cell by its symbolic name. However, since multiple cells may have the same symbolic name another condition is necessary to give uniqueness. The basic idea behind the technique that this system uses to resolve the ambiguity of multiple cells having the same symbolic name is to select the cell with the specified symbolic name that has the same locality as the cell containing the formula referencing the cell by symbolic name. Specifically, if multiple cells have the specified symbolic name then the cell in the smallest region that contains both the cell with the formula and a cell with the specified symbolic name is selected as the referenced cell. In other words, the system searches successive nested containers of the cell containing the formula beginning with the smallest (innermost) container until a container is found that contains a cell with the specified symbolic name. If the found container contains more than one cell with the specified name then the system will display an error message and the user will need to redesign the spreadsheet so that references can be uniquely resolved. This way of referencing cells will be referred to as localized referencing. Note that if a region is copied and pasted then the localized references in formulas naturally references the corresponding cells in the new locality. This provides the same capability as relative cell references but uses user friendly names instead of relative addresses.  
         [0010]     An example will make this clear.  FIG. 1  shows a spreadsheet containing regions. The following below describes the regions and cell contents of the spreadsheet in  FIG. 1 .  
                                                       Range   Name   Contents                           B2:D4   AAA               B3:C4   BBB           B4   CCC   =3*DDD           D2   DDD   111           F2:H6   XXX           F2:G5   YYY           F2   ZZZ   =2*DDD           G5   DDD   333           H6   DDD   123                      
 
         [0011]     To resolve the localized reference “DDD” in the formula “=3*DDD” contained in the cell B 4  The system first searches the region named “BBB” (B 3 :C 4 ) which is the smallest container containing the cell B 4  for a cell named “DDD” and does not find one. Then the system searches the region named “AAA” (B 2 :D 4 ) which is the next smallest container containing the cell B 4  for a cell named “DDD” and finds the cell D 2 . Thus the localized reference “DDD” in the formula “=3*DDD” contained in cell B 4  is resolved to the cell D 2 . The localized reference “DDD” in the formula “=2*DDD” contained in the cell F 2  is resolved to the cell G 5  and not to the cell H 6  since the region named “YYY” (F 2 :G 5 ) is smaller than the region named “XXX” (F 2 :H 6 ).  
         [0012]     A region may be cloned. The original region is called the progenitor. Each cell of the progenitor may be designated as a propagating cell or as a non-propagating cell by the user. After a clone is created the cells of the clone that correspond to non-propagating cells of the progenitor may be modified by the user to customize the clone. A clone is said to be synchronized with its progenitor if the clone has exactly the same member regions at exactly the same relative locations as the progenitor and the contents of all propagating cells of the progenitor are identical to the contents of the corresponding cells of the clone. The contents of a cell is the formula if a cell contains a formula otherwise it is the value of the cell. A clone is permanently linked with its progenitor and a clone can always be synchronized with its progenitor after the progenitor has been edited. After the progenitor has been edited and upon a user request to synchronize the clones with the edited progenitor, the system will propagate to the clones of the progenitor only some of the modifications that have been done to the progenitor during editing and at the same time preserving some of the original contents in the clone. Specifically, changes to the size and/or relative position of member regions contained in the progenitor are propagated to the clones of the progenitor. The size, position and contents of newly created member regions contained in the progenitor are propagated to the clones of the progenitor. Member regions of the clones that correspond to deleted previously existing member regions of the progenitor are deleted from the clones by unbinding those regions from their symbolic name. The contents of the propagating cells of the progenitor are propagated to the corresponding cells of clones of the progenitor. Other cells in the clones of the progenitor will have their contents preserved. The system guarantees that the cells in a clone that correspond to propagating cells in the progenitor will always have the same contents as the corresponding cells in the progenitor. The system will not allow the user to edit a cell in a clone that corresponds to a propagating cell in the progenitor. Hence, the user never needs to worry that a cell in a clone that is propagated to may have an inadvertent wrong value. In particular, the user only needs to check cells that are not propagated to when verifying his spreadsheet. The system can highlight the cells that are not propagated to to make the checking easier. This greatly reduces the effort needed to verify a spreadsheet.  
         [0013]     An example will make this clear.  FIG. 2  shows a spreadsheet containing a clone and its progenitor before the progenitor has been edited. The following table describes the regions and cell contents of the spreadsheet in  FIG. 2 .  
                                                           Range   Name   Contents   Propagating                           B2:D4   AAA                   B2:C3   BBB           B2   CCC   111   no           D4   DDD   =EEE*CCC   yes           D2   EEE    2   yes           B4       =CCC+DDD   yes           F2:H4   AAA           F2:G3   BBB           F2   CCC   707           H4   DDD   =EEE*CCC           H2   EEE    2           F4       =CCC+DDD                      
 
         [0014]     The region F 2 :H 4  is a clone of the progenitor at B 2 :D 4 . Since the cells D 4 , D 2  and B 4  of the progenitor are propagating cells the corresponding cells H 4 , H 2  and F 4  of the clone have identical contents. Note that if a propagating cell in the progenitor contains a formula the corresponding cell in the clone contains the identical formula but not necessarily the same value.  
         [0015]      FIG. 3  shows the spreadsheet of  FIG. 2  after the progenitor has been edited and the clone has been synchronized. The following table describes the regions and cell contents of the spreadsheet in  FIG. 3 .  
                                                           Range   Name   Contents   Propagating                           B2:D4   AAA                   B2:B3   BBB           D4   CCC    111   no           B2   DDD   =EEE*CCC   yes           D2   EEE     3   yes           C3   FFF   10000   no           B4       =CCC+DDD+FFF   yes           F2:H4   AAA           F2:F3   BBB           H4   CCC    707           F2   DDD   =EEE*CCC           H2   EEE     3           G3   FFF   10000           F4       =CCC+DDD+FFF                        
         [0016]     The progenitor “AAA” at B 2 :D 4  has been edited as follows: The region “CCC” at B 2  was moved to D 4 . The region “DDD” at D 4  was moved to B 2 . The region “BBB” at B 2 :C 3  was shrunk to B 2 :B 3 . The content of cell D 2  was changed from “2” to “3”. The region “FFF” was created at C 3  with content “10000”. The content of cell B 4  was changed from “CCC+DDD” to “CCC+DDD+FFF”. After synchronization the clone changes as follows: The region “CCC” at F 2  moves to H 4 . The region “DDD” at H 4  moves to F 2 . The region “BBB” at F 2 :G 3  shrinks to F 2 :F 3 . The content of cell H 2  changes from “2” to “3”. The region “FFF” is created at G 3  with content “10000”. The content of cell F 4  changes from “=CCC+DDD” to “=CCC+DDD+FFF”. Note that the content of the region “CCC” in the clone is unchanged since the corresponding cell in the progenitor is not a propagating cell.  
         [0017]     The system provides a spreadsheet function called SUMNAMED for use in cell formulas. SUMNAMED takes two arguments: the first argument is a pattern for matching symbolic names and the second argument is the name of a container. SUMNAMED will sum the value of all cells that are contained in the container specified by the second argument and have symbolic names that matches the pattern specified by the first argument. The specified container is the smallest container that has the specified symbolic name and contains the cell that contains the formula that has the SUMNAMED expression. The pattern is a regular expression. Although a container may contain cells of many different types, SUMNAMED will sum the value of only those cells in the container with names that match the specified regular expression. For example detail items and subtotals may be contained in the same container but only the detail items may be selectively summed to obtain a grand total if the names of the detail items and the names of the subtotal items can be differentiated by a regular expression.  
         [0018]     An example will make this clear.  FIG. 4  shows a spreadsheet illustrating the use of the SUMNAMED function. The following table describes the regions and cell contents of the spreadsheet in  FIG. 4 .  
                                       Range   Name   Contents                   B2:G6   AAA           B2   BBB     1       C3   BBB     2       D4   BBB     3       C2   CCC1    10       D2   CCC2    20       E2   CCC3    30       G2   DDD1    100       F3   DDD2    200       G4   DDD3    300       B6   EEE1   =SumNamed(“BBB”,“AAA”)       C6   EEE2   =SumNamed(“CCC.+”,“AAA”)       D6   EEE3   =SumNamed(“(CCC.+)|(DDD.+)”,“AAA”)       B8:G12   AAA       B8   BBB   1001       C9   BBB   1002       D10   BBB   1003       C8   CCC1   1010       D8   CCC2   1020       E8   CCC3   1030       G8   DDD1   1100       F9   DDD2   1200       G10   DDD3   1300       B12   EEE1   =SumNamed(“BBB”,“AAA”)       C12   EEE2   =SumNamed(“CCC.+”,“AAA”)       D12   EEE3   =SumNamed(“(CCC.+)|(DDD.+)”,“AAA”)                  
 
         [0019]     The formula “=SUMNAMED(“BBB”,“AAA”)” in cell B 6  sums all the cells with name “BBB” in the container named “AAA” at B 2 :G 6  (This is the smallest container named “AAA” that contains the cell B 6 .) which are the cells B 2 , C 3  and D 4 . The formula “=SUMNAMED(“CCC.+”, “AAA”) in cell C 6  sums all the cells that have names beginning with “CCC” in the container named “AAA” at B 2 :G 6  which are the cells C 2 , D 2  and E 2 . The formula “=SUMNAMED(“(CCC.+)|(DDD.+)”,“AAA”) sums all the cells that have names beginning with “CCC” or “DDD” in the container named “AAA” at B 2 :G 6  which are the cells C 2 , D 2 , E 2 , G 2 , F 3  and G 4 . The formulas in the container named “AAA” at B 8 :G 12  (This container is a clone of the container at B 2 :G 6 .) sum the respective cells in this container. Note that the formulas are the same in both containers but they automatically reference cells in their respective containers.  
         [0020]     The same data may naturally be classified into multiple different hierarchies simultaneously. For example the data may be classified geographically by city, state and country and simultaneously classified by time by day, month, quarter and year. This system supports multiple hierarchies on the same spreadsheet using planes. Each spreadsheet has four planes: Main, Aux 1 , Aux 2  and Aux 3 . Each plane supports one hierarchy. When trying to resolve a reference to a region this system searches all the containers of the cell that has the formula with the reference in all the planes in order by size smallest first until the reference is resolved.  
         [0021]     An example will make this clear.  FIG. 5  shows a spreadsheet with two planes: Main and Aux 1 . The following table describes the regions and cell contents of the spreadsheet in  FIG. 5 .  
                                           Range   Name   Contents   Plane                   C3   AMOUNT    11   Main, Aux1       C4   AMOUNT   130   Main, Aux1       D3   AMOUNT    10   Main, Aux1       D4   AMOUNT   120   Main, Aux1       C2:C5   SEASON       Main       D2:D5   SEASON       Main       B3:E3   REGION       Aux1       B4:E4   REGION       Aux1       C5       =SumNamed(“AMOUNT”,               “SEASON”)       D5       =SumNamed(“AMOUNT”,               “SEASON”)       E3       =SumNamed(“AMOUNT”,               “REGION”)       E4       =SumNamed(“AMOUNT”,               “REGION”)                  
 
         [0022]     The Main plane contains regions for the seasons: Spring and Summer. The Aux 1  plane contains regions for the geographical regions: North and South. The “SEASON” regions (C 2 :C 5  and D 2 :D 5 ) and the “REGION” regions (B 3 :E 3  and B 4 :E 4 ) cannot exists in the same plane since they partially overlap. The reference to “SEASON” in the formula “=SUMNAMED(AMOUNT, SEASON)” in cell C 5  is resolved to the “SEASON” region at C 2 :C 5  since that region is the smallest region named “SEASON” that contains the cell C 5 .