Method and system in an electronic spreadsheet for persistently self-replicating multiple ranges of cells through a copy-paste operation

<i>Technical field of the invention</i>

The present invention relates to the field of information processing by digital computers, and more particularly to a method and system, in an electronic spreadsheet, for persistently self-replicating multiple ranges of cells through a copy-paste operation.

<i>Background art</i>

Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organising data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.

With the advent of microcomputers, a solution was forthcoming in the form of "electronic spreadsheets." Better known simply as "spreadsheets," these software programs provide a computerised replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.

A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this "pad" exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, a letter to each column, and another letter to each sheet (or page) of the spreadsheet. To reference a location at column A and row 1 of the second page (i.e., the upper-left hand corner), for example, the user types in "B:A1". In this manner, the spreadsheet defines an addressable storage location or "cell" at each intersection of a row with a column within a given page.

Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or "formulas" specifying calculations to be performed on the numbers stored in spreadsheet cells. Such spreadsheet cells can also be defined and named as a range as long as they are arranged as a connex set of cells. A typical example of such a named range simply corresponds to a regular table found in an accountant's pad. In this fashion, range names can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature.

Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform "what-if" scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve "what-if' problems, that is, changing an input and seeing what happens to an output.

Cell ranges are used to automate the computations in a spreadsheet. Whether cells or cell ranges are named or not, they can be referenced within a formula either by a "relative" or an "absolute" reference. Such a reference can be the address of the referenced cell range, or the name of the referenced cell range if it turns that this cell range is named. It is common to find in electronic spreadsheet based applications some large tables which are organised according to a structured way. This structure typically results in organising rows, columns and sheets in such a way that the content of each of the cells within a given column and within a given sheet can be obtained as the result of a copy-paste operation where the source copied cell is any cell within this same column and same sheet. In such typical situations, this source cell can contain a formula referencing in a relative or absolute way one or several other cells, so that each of the other cells within the same column of the same sheet will also contain the same formula where the absolute references will be kept unchanged and where the relative references will point to other relative cells. Such a typical situation is illustrated in FIG <b>3A</b> where a table is used to compute a sales item price according to some input data. In this table, the content of the cell with address C6 (column entitled "Unit Cost") is for instance equal to the formula "@CostOf(B6)" where @CostOf is a dedicated function providing the cost of an item passed as parameter. In the same table, the content of the cell with address G6 (column entitled "Exchange rate") is for instance equal to the formula "@RateOf(F6)" where @RateOf is a dedicated function returning the exchange rate for a currency passed as parameter. In the same table, the content of the cell with address I6 (column entitled "Price") is for instance equal to the formula "C6*D6*G6/(1-$PROFIT)" where "PROFIT" is the name given to the cell range with address I3 where is recorded the profit figure. The content of each cell within the "Unit Cost" table can be obtained by copy-pasting the cell with address C6, so that the content of the cell with address Cx (where x takes the values 7 to 10) is found equal to "@CostOf(Bx)". This way, each of the cells with address C6 to C10 is virtually a "replicate" of all the other cells with address C6 to C10 through a copy-paste operation, meaning that any cell within this set can be derived from any other one within the same set through a copy-paste operation. Similarly, the content of the cells with address Gx and with address Ix are obtained by respectively copy-pasting the content of the cells with address G6 and with address I6, resulting into a content equal respectively to "@RateOf(Fx)" and to "Cx*Dx*Gx/(1-$PROFIT)". Doing so, the cells with address G6 to G10 and the cells with address I6 to I10 are virtually "self replicating" through a copy-paste operation.

The copy-paste operation is thus a powerful tool for applying in many different cells, or ranges of cells, the content of a given cell or of a given range of cells. Nevertheless this copy-paste operation presents some limitations, as outlined hereafter. Let assume that in our example the content of the cells within a table column needs to be updated to reflect some structural change of the table it belongs to. Such a structural change is illustrated in FIG <b>3B</b> where the profit parameter (used to derive a price from a cost) is no longer constant for all sold items (as shown in FIG <b>3A</b> with the cell of address I3, and named "PROFIT"), but depends on the sold item itself, as represented in the table by the cells within the column entitled "Profit". Under this new rule, the content of the cell with address I6 (within the column entitled "Price") is now equal to the formula "C6*D6*G6/(1-H6)". In order to reflect this table structural update in the other cells of the same "Price" column, it is necessary to reapply the copy-paste operation from the top column cell to all the other column cells following the same logic, that is the cells with address I7 to I10 as shown in FIG <b>3B.</b> More generally, this operation must be carefully done each time a given range of cells content is updated and must be applied to all the other ranges of cells which have been initially self replicated with this given range of cells through a copy-paste operation. With large and complex spreadsheets, such a task may take quite a long time and is error prone because the spreadsheet user may miss some of the ranges of cells where the copy-paste operation must be reapplied. If it is the case, then the resulting spreadsheet provides erroneous results. The present invention offer a powerful and efficient solution to this problem by defining a method and a system for persistently self-replicating multiple ranges of cells through a copy-paste operation.

<i>Summary of the invention</i>

The present invention relates to the field of information processing by digital computers, and more particularly to a method and system for persistently self-replicating multiple ranges of cells through a copy-paste operation, in a multi dimensional spreadsheet comprising a plurality of cells identified by a cell address along each dimension, a range of cells comprising one or a plurality of cells The method comprises the steps of: <ul id="ul0001" list-style="bullet" compact="compact"><li>defining a set of ranges of cells, each range of cells having the same size;</li><li>each time the content of a range of cells belonging to this set is changed, automatically performing a self-replication operation, the self-replication operation comprising the steps of: <ul id="ul0002" list-style="bullet" compact="compact"><li>copying the changed range of cells onto a buffer;</li><li>determining the set of ranges of cells to which the changed range of cells belongs to;</li><li>identifying the ranges of cells belonging to the set;</li><li>pasting the content of the buffer in each of identified range of cells belonging to the set.</li></ul></li></ul>

<i>Brief description of</i> the <i>drawings</i>

The novel and inventive features believed characteristics of the invention are set forth in the appended claims. The invention itself, however, as well as a preferred mode of use, further objects and advantages thereof, will best be understood by reference to the following detailed description of an illustrative detailed embodiment when read in conjunction with the accompanying drawings, wherein : <ul id="ul0003" list-style="bullet"><li>Figure <b>1A</b> is a schematic view of a computer system in which the present invention may be embodied.</li><li>Figure <b>1B</b> is a schematic view a software system including an operating system, an application software, and a user interface for carrying out the present invention.</li><li>Figure <b>1C</b> illustrates the basic architecture and functionality of a graphical user interface in which the present invention may be embodied.</li><li>Figure <b>2A</b> shows a spreadsheet notebook interface according to the preferred embodiment of the present invention.</li><li>Figure <b>2B</b> shows the toolbar component of the notebook interface shown in Figure <b>2A.</b></li><li>Figures <b>2C</b> and <b>2D</b> show page identifiers for rapidly accessing and manipulating individual pages of the notebook interface shown in Figure <b>2A.</b></li><li>Figures <b>3A</b> and <b>3B</b> illustrate a typical spreadsheet structure used in the preferred embodiment of the present invention.</li><li>Figure <b>4</b> illustrates the structure of the persistent self-replication table, according to the preferred embodiment of the present invention.</li><li>Figures <b>5A, 5B,</b> illustrate a preferred spreadsheet user interface for invoking the persistent self-replicating operation, according to the present invention.</li><li>Figure <b>6</b> is a flow chart illustrating a preferred method for managing objects involved in Persistent Self-Replication operations, according to the present invention.</li><li>Figure <b>7</b> is a flow chart illustrating a preferred method for performing a persistent copy-paste operation, according to the present invention.</li></ul>

<i>Detailed description of the preferred embodiment</i>

<u>SYSTEM HARDWARE</u>

As shown in FIG. <b>1A,</b> the present invention may be embodied on a computer system <b>100</b> comprising a central processor <b>101,</b> a main memory <b>102,</b> an input/output controller <b>103,</b> a keyboard <b>104,</b> a pointing device <b>105</b> (e.g., mouse, track ball, pen device, or the like), a display device <b>106,</b> and a mass storage <b>107</b> (e.g., hard disk). Additional input/output devices, such as a printing device <b>108,</b> may be included in the system <b>100</b> as desired. As illustrated, the various components of the system <b>100</b> communicate through a system bus <b>110</b> or similar architecture. In a preferred embodiment, the computer system <b>100</b> includes an IBM-compatible personal computer, which is available from several vendors (including International Business Machine - IBM Corporation of Armonk, N.Y.).

Illustrated in FIG. <b>1B,</b> a computer software system <b>150</b> is provided for directing the operation of the computer system <b>100.</b> Software system <b>150,</b> which is stored in system memory <b>102</b> and on disk memory <b>107,</b> includes a kernel or operating system <b>151</b> and a shell or interface <b>153.</b> One or more application programs, such as application software <b>152,</b> may be "loaded' (i.e., transferred from storage <b>107</b> into memory <b>102</b>) for execution by the system <b>100.</b> The system <b>100</b> receives user commands and data through user interface <b>153;</b> these inputs may then be acted upon by the system <b>100</b> in accordance with instructions from operating module <b>151</b> and/or application module <b>152.</b> The interface <b>153,</b> which is preferably a graphical user interface (GUI), also serves to display results, whereupon the user may supply additional inputs or terminate the session. In a preferred embodiment, operating system <b>151</b> and interface <b>153</b> are Microsoft Win95, available from Microsoft Corporation of Redmond, Wash. Application module <b>152,</b> on the other hand, includes a spreadsheet notebook of the present invention as described in further detail herein below.

<u>INTERFACE</u>

A. Introduction

The following description will focus on the presently preferred embodiments of the present invention, which are embodied in spreadsheet applications operative in the Microsoft Win95 environment. The present invention, however, is not limited to any particular application or any particular environment. Instead, those skilled in the art will find that the system and methods of the present invention may be advantageously applied to a variety of system and application software, including database management systems, word processors, and the like. Moreover, the present invention may be embodied on a variety of different platforms, including Macintosh, UNIX, NextStep, and the like. Therefore, the description of the exemplary embodiments which follows is for purposes of illustration and not limitation.

Referring now to FIG. <b>1C,</b> the system <b>100</b> includes a windowing interface or workspace <b>160.</b> Window <b>160</b> is a rectangular, graphical user interface (GUI) for display on screen <b>106;</b> additional windowing elements may be displayed in various sizes and formats (e.g., tiled or cascaded), as desired. At the top of window <b>160</b> is a menu bar <b>170</b> with a plurality of user-command choices, each of which may invoke additional submenus and software tools for use with application objects. Window <b>160</b> includes a client area <b>180</b> for displaying and manipulating screen objects, such as graphic object <b>181</b> and text object <b>182.</b> In essence, the client area is a workspace or viewport for the user to interact with data objects which reside within the computer system <b>100.</b>

Windowing interface <b>160</b> includes a screen cursor or pointer <b>185</b> for selecting and otherwise invoking screen objects of interest. In response to user movement signals from the pointing device <b>105,</b> the cursor <b>185</b> floats (i.e., freely moves) across the screen <b>106</b> to a desired screen location. During or after cursor movement, the user may generate user-event signals (e.g., mouse button "clicks" and "drags") for selecting and manipulating objects, as is known in the art. For example, Window <b>160</b> may be closed, re-sized, or scrolled by "clicking" (selecting) screen components <b>172, 174/5,</b> and <b>177/8,</b> respectively.

In a preferred embodiment, screen cursor <b>185</b> is controlled with a mouse device. Single-button, double-button, or triple-button mouse devices are available from a variety of vendors, including Apple Computer of Cupertino, Calif., Microsoft Corporation of Redmond, Wash., and Logitech Corporation of Fremont, Calif., respectively. More preferably, screen cursor control device <b>105</b> is a two-button mouse device, including both right and left "mouse buttons."

Programming techniques and operations for mouse devices are well documented in the programming and hardware literature; see e.g., <i>Microsoft Mouse Programmer's Reference,</i> Microsoft Press, 1989. The general construction and operation of a GUI event-driven system, such as Windows, is also known in the art: see, e.g., Petzold, C., <i>Programming Windows,</i> Second Edition, Microsoft Press, 1990. The disclosures of each are hereby incorporated by reference.

B. Preferred interface

Shown in FIG. 2<b>A,</b> a spreadsheet notebook interface of the present invention will now be described The spreadsheet notebook or workbook of the present invention includes a notebook workspace <b>200</b> for receiving, processing, and presenting information, including alphanumeric as well as graphic information. Notebook workspace <b>200</b> includes a menu bar <b>210,</b> a toolbar <b>220,</b> a current cell indicator <b>230,</b> an input line <b>231,</b> a status line <b>240,</b> and a notebook window <b>250.</b> The menu bar <b>210</b> displays and invokes, in response to user inputs, a main level of user commands. Menu <b>210</b> also invokes additional pull down menus, as is known in windowing applications. Input line <b>231</b> accepts user commands and information for the entry and editing of cell contents, which may include data, formulas, macros, and the like. Indicator <b>230</b> displays an address for the current cursor (i.e., active cell) position, or the address or name of a selected named range (i.e. active selection). At the status line <b>240,</b> system <b>100</b> displays information about the current state of the workbook; for example, a "READY" indicator means that the system is ready for the user to select another task to be performed.

The toolbar <b>220,</b> shown in further detail in FIG. 2<b>B,</b> comprises a row or palette of tools which provide a quick way for the user to choose commonly-used menu commands or properties. In an exemplary embodiment, toolbar <b>220</b> includes file manipulation buttons <b>221,</b> printing buttons <b>222,</b> an undo button <b>223,</b> cut, copy, and paste buttons <b>224,</b> information pop-up window buttons tool <b>225,</b> a named range selection button <b>226,</b> a style copy button <b>227,</b> a column re-sizing button <b>228,</b> and a sum button <b>229.</b> The functions of these buttons are suggested by their names. For instance, buttons <b>224</b> cut, copy and paste data and objects to and from Windows' clipboard. The same actions are also available as corresponding commands in the Edit menu (available from menu bar <b>210</b>).

The notebook, which provides an interface for entering and displaying information of interest, includes a plurality of spreadsheet pages. Each page may include conventional windowing features and operations, such as moving, re-sizing, and deleting. In a preferred embodiment, the notebook includes 256 spreadsheet pages, all of which are saved as a single disk file on the mass storage <b>107.</b> Workspace <b>200</b> may display one or more notebooks, each sized and positioned (e.g., tiled, overlapping, and the like) according to user-specified constraints.

Each spreadsheet page of a notebook includes a 2-D spread. Page A from the notebook <b>200,</b> for example, includes a grid in row and column format, such as row 3 and column F. At each row/column intersection, a box or cell (e.g., cell <b>C4</b>) is provided for entering, processing, and displaying information in a conventional manner. Each cell is addressable, with a selector being provided for indicating a currently active one (i.e., the cell that is currently selected).

As shown in FIGS. <b>2C-D,</b> individual notebook pages are identified by page identifiers <b>260,</b> preferably located along one edge of a notebook. In a preferred embodiment, each page identifier is in the form of a tab member (e.g., members <b>261a, 262a, 263a</b>) situated along a top edge of the notebook. Each tab member may include representative indicia, such as textual or graphic labels, including user selected titles representing the contents of a corresponding page. In FIG. <b>2C,</b> the tab members <b>260</b> are set to their respective default names. For example, the first three tab members (members <b>261a, 262a, 263a</b>) are respectively set to A, B, and C. Tab members are typically given descriptive names provided by the user, however. As shown in FIG. <b>2D,</b> for example, the first three tab members have now been set to "Contents" (tab member <b>261b</b>), "Summary" (tab member <b>262b</b>), and "Jan" (tab member <b>263b</b>). In a similar manner, the remaining tabs are set to subsequent months of the year. In this manner, the user associates the page identifiers with familiar tabs from an ordinary paper notebook. Thus, the user already knows how to select a page or spread of interest: simply select the tab corresponding to the page (as one would do when selecting a page from a paper notebook).

In addition to aiding in the selection of an appropriate page of information, the user-customizable page identifiers serve aid in the entry of spreadsheet named range addresses. For example, when entering a formula referring to a named range of cells on another page, the user may simply use the descriptive page name in the named range address, thus making it easier for the user to understand the relationship of the cell(s) or information being referenced.

A general description of the features and operation of the spreadsheet notebook interface may be found in Quattro Pro for Windows <i>(Getting Started, User's Guide and Building Spreadsheet Applications),</i> available from Borland International.

<u>PERSISTENT SELF-REPLICATING OPERATION</u>

A. Introduction

As the power of spreadsheet environments has increased since several years, it is today possible to develop complex custom applications solely based on spreadsheets, as opposed to applications developed with general purpose programming languages like C++ or VisualBasic from Microsoft Corporation. This can be achieved thanks to the spreadsheet imbedded tools such as macro languages, script languages and formulas. In large spreadsheets, it is common to find structured tables where the content of some cells are directly derived from the contents of other cells thanks to formulas which translate the relationship between these cells. Such formulas can be quite complex, so that it is advantageous to copy-paste such a formula, once established, from a given cell onto all the other cells where the same relationship exists. If this relationship evolves during the life of a spreadsheet, then the spreadsheet user must first update a first cell content (typically rewriting the formula it holds), and then the spreadsheet user must again perform a copy-paste operation between this first cell and all the cells whose content was previously obtained from the reference cell content with a copy-paste operation. Besides the time spent by the user spreadsheet to perform this operation, there is a risk for performing this new copy-paste operation to the wrong set of cells: either some cells deserving to be again copy-pasted may be missed, or some cells may be copy-pasted while they should not. In both cases, the resulting spreadsheet has been wrongly updated, so that it provides wrong computed results.

The present invention offer a user-friendly solution to this problem by defining a method and system allowing to persistently self-replicating multiple ranges of cells through a copy-paste operation.

B. Self-Replication Manager

In contrast to just-described conventional tools, the present invention provides a more powerful, user-friendly and interactive approach for persistently self-replicating multiple ranges of cells through a copy-paste operation in a form of a Self-Replication manager. The manager automatically allows the spreadsheet user to : <ul id="ul0004" list-style="bullet" compact="compact"><li>create or rename or delete a set of persistently self-replicating ranges of cells, or</li><li>add or suppress a given range of cells to / from a set of persistently self-replicating ranges of cells, and</li><li>self-replicate any content update from a given range of cells belonging to a set of persistently self-replicating ranges of cells to all the other ranges of cells belonging to the same set of persistently self-replicating ranges of cells.</li></ul> For more clarity, a persistently self-replicating range of cells which can take advantage of the present invention will be called <i>"persistently self-replicating range"</i> or <i>"PSRR",</i> and a set of PSRR will be called <i>"persistently self-replicating set"</i> or <i>"PSRS"</i>

C. Persistently self-replicating range and persistently self-replicating set

In a preferred embodiment, PSRR can be easily identified on the display device <b>106</b> within the work area <b>180</b> of the window <b>160</b> by using some specific cell attributes, such as a font style or font colour or background colour or border line style or border line colour or background pattern, etc... In a preferred embodiment, the background pattern of a PSRR is set to a first pattern value referred to as PATTERN_PSRR. PSRS are uniquely identified by a logical identifier which can take multiple forms. In a preferred embodiment of the present invention, PSRS are uniquely identified by a name corresponding to a character string of limited length.

D. Scenario

In a preferred embodiment, the present invention can be used in two steps : <ul id="ul0005" list-style="bullet" compact="compact"><li>The first step occurs when the spreadsheet user decides, based on some criteria not developed here, to take advantage of the present invention by using the self-replication manager for either creating, or deleting, or renaming a PSRS ; or for either adding or removing a PSRR to / from a PSRS. If it is the case, the spreadsheet user can follow in sequence the following steps :</li><li>First the spreadsheet user optionally selects a range of cells by using conventional means such as (but not limited to) the pointing device <b>105</b> or the keyboard <b>104.</b></li><li>Then the spreadsheet user invokes an extension of the regular spreadsheet editing facilities thanks to conventional means available in spreadsheet environment, such as (but not limited to) dedicated push-buttons, keyboard entry short cuts, menu or sub menu entries. This extension of the regular editing facilities corresponds to a specific command called <b><i>"Self</i>_<i>Replication_Manager".</i></b> In a preferred embodiment of the present invention, this <b><i>Self_Replication_Manager</i></b> command is invoked by clicking with the pointing device <b>105</b> on a menu entry <b>501</b> "Self Replication" within the conventional "Edit" menu <b>500</b> of an electronic spreadsheet, as shown in FIG <b>5A.</b> It results in displaying on the display device <b>106</b> a specific Self-Replication Dialog Box <b>600,</b> as shown in FIG <b>5B.</b></li><li>Then the spreadsheet user can take advantage of the different tools available within the Self-Replication Dialog Box <b>600,</b> according to the following list: <ul id="ul0006" list-style="bullet" compact="compact"><li>The "Name" text box <b>511</b> and the "Create" push-button <b>517</b> can be used to create a new PSRS. For this purpose the spreadsheet user first fills this "Name" text box <b>511</b> with the name of the new PSRS to be created and then clicks on the "Create" push-button <b>517.</b> As a result, the newly created PSRS now appears within the "Existing PSRS" list box <b>512.</b></li><li>The "Existing PSRS" list box <b>512</b> and the "Delete" push-button <b>518</b> can be used to delete an existing PSRS. For this purpose, the spreadsheet user first selects within the "Existing PSRS" list box <b>512</b> the name of the PSRS to be deleted (if not visible within the "Existing PSRS" list box <b>512,</b> the spreadsheet user can simply use the scroll bar <b>523</b> with the pointing device <b>105</b> to let the desired PSRS name appear within the "Existing PSRS" list box <b>512</b>), and then clicks on the "Delete" push-button <b>518.</b> As a result, the just deleted PSRS disappears from the "Existing PSRS" list box <b>512.</b></li><li>The "Name" text box <b>511,</b> the "Existing PSRS" list box <b>512</b> and the "Rename" push-button <b>519</b> can be used to change the name of an existing PSRS. For this purpose, the spreadsheet user first selects within the "Existing PSRS" list box <b>512</b> the name of the PSRS to be renamed (if not visible within the "Existing PSRS" list box <b>512,</b> the spreadsheet user can simply use the scroll bar <b>523</b> with the pointing device <b>105</b> to let the desired PSRS name appear within the "Existing PSRS" list box <b>512</b>), then fills the "Name" text box <b>511</b> with the new name of the PSRS and then clicks on the "Rename" push-button <b>518.</b> As a result, the new name of the existing PSRS now appears within the "Existing PSRS" list box <b>512.</b></li><li>The "Existing PSRR members" list box <b>513</b> can be used to visualise all the PSRR belonging to the PSRS currently selected within the "Existing PSRS" list box <b>512.</b> For this purpose, the spreadsheet user first selects within the "Existing PSRS" list box <b>512</b> the name of the PSRS to be visualised (if not visible within the "Existing PSRS" list box <b>512,</b> the spreadsheet user can simply use the scroll bar <b>523</b> with the pointing device <b>105</b> to let the desired PSRS name appear within the "Existing PSRS" list box <b>512</b>), and then can click on the scroll bar 524 with the pointing device <b>105</b> to let appear in the "Existing PSRR members" list box <b>513</b> every PSRR belonging to the PSRS selected in the "Existing PSRS" list box <b>512.</b></li><li>The "Existing PSRR members" list box <b>513</b> and the "Suppress" push-button <b>521</b> can be used to remove a given PSRR from the PSRS currently selected within the "Existing PSRS" list box <b>512.</b> For this purpose, the spreadsheet user first selects within the "Existing PSRS" list box <b>512</b> the name of the PSRS from which one member must be removed (if not visible within the "Existing PSRS" list box <b>512,</b> the spreadsheet user can simply use the scroll bar <b>523</b> with the pointing device <b>105</b> to let the desired PSRS name appear within the "Existing PSRS" list box <b>512</b>), then selects within the "Existing PSRR members" list box <b>513</b> the name of the PSRR to be removed (if not visible within the "Existing PSRR members" list box <b>513,</b> the spreadsheet user can simply use the scroll bar <b>524</b> with the pointing device <b>105</b> to let the desired PSRR name appear within the "Existing PSRR members" list box <b>513</b>), and then clicks on the "Suppress" push-button <b>521.</b> As a result, the just deleted PSRR disappears from the "Existing PSRR" list box <b>513,</b> and its background pattern is changed from the value PATTERN_PSRR to its original value (before it was turned as a PSRR).</li><li>The "Range" text box <b>514,</b> the "Select" push-button <b>522</b> and the "Add" push-button <b>520</b> can be used to add a new PSRR to the PSRS currently selected within the "Existing PSRS" list box <b>512.</b> For this purpose the spreadsheet user first checks that the "Range" text box <b>514</b> holds the address of the range of cells to be added. By default, the "Range" text box <b>514</b> contains the address of the range of cells which was selected in the electronic spreadsheet just before invoking the <b><i>Self_Replication_Manager</i></b> command. The user can change this default range by clicking on the "Select" push-button <b>522</b> and then use the pointing device <b>105</b> to select the desired range of cells within the electronic spreadsheet. When the "Range" text box <b>514</b> holds the address of the right range of cells to be added, the user clicks on the "Add" push-button <b>520.</b> As a result, the newly added PSRR now appears within the "Existing PSRR members" list box <b>513,</b> and its background pattern is changed into a new pattern with value PATTERN_PSRR.</li><li>The "Cancel" push-button <b>516</b> and the "OK" push-button <b>515</b> can be used by the spreadsheet user to close the Self-Replication Dialog Box <b>600.</b></li></ul></li><li>The second step occurs when the spreadsheet user updates a cell belonging to a PSRR which is itself a member of a PSRS: <ul id="ul0007" list-style="bullet" compact="compact"><li>If the spreadsheet user updates the content of a cell belonging to a PSRR, the self-replication manager invokes by itself a specific command called <b><i>"Persistent_Self_Replicate"</i></b> which automatically reflects this update in all the other PSRR belonging to the same PSRS than the updated PSRR. This <b><i>"Persistent_Self_Replicate"</i></b> operation is fully automated, without involvement of the spreadsheet user, and is itself based on a copy-paste operation applied by the self-replication manager between the updated PSRR and all the other PSRR belonging to the same PSRS.</li></ul></li></ul>

E. Persistent Self-Replication Table

The decision to create, delete or rename a PSRS or to add or suppress a PSRR to / from a PSRS belongs to the spreadsheet user. When such an operation occurs, a common repository, called <i>the "Persistent Self-Replication Table",</i> is used to record the data required by this operation. This Persistent Self-Replication Table is preferably saved on a non volatile memory (typically but not necessary as part of the spreadsheet disk file on the mass storage <b>107</b>)

Referring now to FIG. <b>4,</b> the Persistent Self-Replication Table <b>400</b> corresponds to a logical simple structure made of several records <b>401,</b> each of them corresponding to a PSRR and including five fields: <ul id="ul0008" list-style="bullet" compact="compact"><li>The <i>"PSRS Name"</i><b>402</b> field is used for identifying uniquely the PSRS associated to the current record <b>401.</b></li><li>The <i>"PSRR Address"</i><b>403</b> field is used for identifying uniquely the PSRR within the spreadsheet. For instance, the <i>"PSRR Address"</i> can correspond to the conventional address structure Sheet:RowColumn..Sheet:RowColumn associated to every range of cells (For example D:E10..D:G20 with D as Sheet name, E and G as Row name/number, 10 and 20 as Column name/number).</li><li>The "<i>PSRR Pattern"</i> field <b>404</b> records the background pattern of the PSRR, before being member of a PSRS.</li><li>The <i>"Set Index"</i> field <b>405</b> is used for navigating within the Self-Replication Table <b>400.</b></li><li>The <i>"Range Index"</i> field <b>406</b> is used for navigating within the Self-Replication Table <b>400.</b></li></ul>

The record <b>410</b> located at the beginning of the Persistent Self-Replication Table <b>400</b> is referred to as the top record.

In the preferred embodiment, the Persistent Self-Replication Table <b>400</b> is explicitly included within the spreadsheet file itself, but other obvious implementations can be used instead.

F. Methods

F.1 Self_Replication_Manager method

The method of handling user requests to take advantage of the present invention is detailed in flowchart <b>600</b> of FIG <b>6.</b> This method can be seen as the processing of the <b><i>Self_Replication_Manager</i></b> command used for creating or deleting or renaming a PSRS and for adding or suppressing a PSRR to / from a PSRS. The method comprises the following steps : <ul id="ul0009" list-style="bullet" compact="compact"><li>At step <b>601,</b> the method is in its default state, waiting for an event to initiate the process.</li><li>At step <b>602,</b> an event is detected, as a result of a user action. This action can be for instance a specific combination of key on the keyboard <b>104,</b> or the click of the pointing device <b>105</b> on a specific button, or any other similar means not further specified here.</li><li>At step <b>603,</b> local variables are initialised: the <b><i>PSRSindex</i></b> variable is set to the value 0, the <b><i>PSRRindex</i></b> variable is set to the value 0, the <b><i>NewName</i></b> variable is set to the value "" (empty string), and the <b><i>NewRange</i></b> variable is set to the character string representing the address of the electronic spreadsheet current selection.</li><li>At step <b>604,</b> the Self-Replication Dialog Box <b>510</b> is displayed on the display device <b>106.</b> The "Name" text box <b>511</b> is filled with the variable <b><i>NewName.</i></b> The "Existing PSRS" list box <b>512</b> is filled with the names found in the <i>"PSRS Name"</i> fields <b>402</b> of the various records <b>401</b> of the Self-Replicating Table <b>400.</b> Within the "Existing PSRS" list box <b>512,</b> the active item corresponds to the record <b>401</b> whose <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex.</i></b> The "Existing PSRR members" list box <b>513</b> is filled with the addresses found in the <i>"PSRR Address"</i> fields <b>403</b> of the various records <b>401</b> of the Self-Replicating Table <b>400</b> for which the <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex.</i></b> Within the "Existing PSRR members" list box <b>513,</b> the active item corresponds to the record <b>401</b> whose <i>"Range Index"</i> field <b>406</b> is equal to <b><i>PSRRIndex.</i></b> The "Range" text box <b>514</b> is filled with the variable <b><i>NewRange.</i></b></li><li>At step <b>605,</b> the method is waiting for any user action on the Self-Replication Dialog Box <b>510.</b> Such user action is typically resulting from a click with the pointing device <b>105,</b> but take other similar forms such as, but not limited to a specific combination of key on the keyboard <b>104,</b> or any other similar means not further specified here.</li><li>At step <b>606,</b> a user action on the Self-Replication Dialog Box <b>510</b> is detected. If the user action is a change of the content of the "Name" text box <b>511,</b> then control is given to step <b>609;</b> if the user action is a selection with the pointing device <b>105</b> of an item within the "Existing PSRS" list box <b>512,</b> then control is given to step <b>622;</b> if the user action is a selection with the pointing device <b>105</b> of an item within the "Existing PSRR members" list box <b>513,</b> then control is given to step <b>621;</b> if the user action is a change of the content of the "Range" text box <b>514,</b> then control is given to step <b>610;</b> if the user action is click on the push-button "OK" <b>515,</b> then control is given to step <b>607;</b> if the user action is click on the push-button "Cancel" <b>516,</b> then control is given to step <b>607;</b> if the user action is click on the push-button "Create" <b>517,</b> then control is given to step <b>627;</b> if the user action is click on the push-button "Delete" <b>518,</b> then control is given to step <b>626;</b> if the user action is click on the push-button "Rename" <b>519,</b> then control is given to step <b>608;</b> if the user action is click on the push-button "Add" <b>520,</b> then control is given to step <b>624;</b> if the user action is click on the push-button "Suppress" <b>521,</b> then control is given to step <b>625;</b> if the user action is click on the push-button "Select" <b>522,</b> then control is given to step <b>623;</b> if the user action is a click on the up arrow of the scroll bar <b>523,</b> then control is given to step <b>618;</b> if the user action is a click on the down arrow of the scroll bar <b>523,</b> then control is given to step <b>611;</b> if the user action is a click on the up arrow of the scroll bar <b>524,</b> then control is given to step <b>620;</b> if the user action is a click on the down arrow of the scroll bar <b>524,</b> then control is given to step <b>612.</b></li><li>At step <b>607,</b> the Self-Replication Dialog Box <b>510</b> is closed, so that it disappears from the display device <b>106,</b> and control is given back to the initial step <b>601</b> for treating any future Self_Replication_Manager command.</li><li>At step <b>608,</b> in the Self-Replication Table <b>400,</b> all the records <b>401</b> having a <i>"Set Index"</i> field <b>405</b> equal to <b><i>PSRSIndex</i></b> are updated by replacing their <i>"PSRS name"</i> field <b>402</b> by <b><i>NewName.</i></b> Then control is given to step <b>631.</b></li><li>At step <b>609,</b> the character string specified by the spreadsheet user in the "Name" text box <b>511</b> is checked against all the already defined PSRS names, as recorded in the <i>"PSRS Name"</i> field <b>402</b> of all the records <b>401</b> of the Self-Replication Table <b>400.</b> If the character string is new, that is does not match with any already defined name, then control is given to step <b>616;</b> otherwise control is given to step <b>613.</b></li><li>At step <b>610,</b> the character string specified by the spreadsheet user in the "Range" text box <b>514</b> is checked against a set of rules not detailed here to determine if it is or not a valid range address. Such rules are typically implementation dependent and thus do not belong to the scope of the present invention. If the result of this checking is that this character string found as valid, then control is given to step <b>617,</b> otherwise control is given to step <b>613.</b></li><li>At step <b>611,</b> the variable <b><i>PSRSIndex</i></b> is decremented, unless the minimum value (equal to zero) is already reached. Then control is given to step <b>619.</b></li><li>At step <b>612,</b> the variable <b><i>PSRRIndex</i></b> is decremented, unless the minimum value (equal to zero) is already reached. Then control is given to step <b>631.</b></li><li>At step <b>613,</b> an error message notification is issued for warning the user that the character string checked at step <b>609</b> or <b>610</b> has not been found correct. This can typically be done by displaying on the display device <b>106</b> an error message in a pop-up window, but any other similar means could be used instead, without departing from the spirit of the present invention.</li><li>At step <b>614,</b> the method is waiting for a user acknowledgement, meaning that the error message notification of step <b>613</b> has been received by the spreadsheet user.</li><li>At step <b>615,</b> the user acknowledgement is detected. This can typically correspond to the click, thanks to the pointing device <b>105,</b> on a "OK" push-button within the pop-up window typically displayed during the step <b>613,</b> but other similar means can be used instead, without departing from the spirit of the present invention. Then control is given to step <b>631.</b></li><li>At step <b>616,</b> the value of the variable <b><i>NewName</i></b> is set equal to the character string within the "Name" text box <b>511.</b> Then control is given to step <b>631.</b></li><li>At step <b>617,</b> the value of the variable <b><i>NewRange</i></b> is set equal to the character string within the "Range" text box <b>514.</b> Then control is given to step <b>631.</b></li><li>At step <b>618,</b> the variable <b><i>PSRSIndex</i></b> is incremented, unless the maximum value (equal to the number of different values of the "PSRS Name" field <b>401</b>) is already reached.</li><li>At step <b>619,</b> the variable <b><i>PSRRIndex</i></b> is set equal to zero. Then control is given to step <b>631.</b></li><li>At step <b>620,</b> the variable <b><i>PSRRIndex</i></b> is incremented, unless the maximum value (equal to the number of records <b>401</b> whose <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex</i></b>) is already reached. Then control is given to step <b>631.</b></li><li>At step <b>621,</b> the variable <b><i>PSRRindex</i></b> is set equal to the value of the <i>"Range Index"</i> field <b>406</b> of the record <b>401</b> of the Self-Replication table <b>400</b> corresponding to the user selection of the "Existing PSRR members" list box <b>513.</b> Then control is given to step <b>631.</b></li><li>At step <b>622,</b> the variable <b><i>PSRSindex</i></b> is set equal to the value of the <i>"Set Index"</i> field <b>405</b> of the record <b>401</b> of the Self-Replication table <b>400</b> corresponding to the user selection of the "Existing PSRS" list box <b>512.</b> Then control is given to step <b>631.</b></li><li>At step <b>623,</b> the method uses conventional means to let the user select a range within the spreadsheet. Such means may for instance rely on a pop-up window within which the user enters through the keyboard <b>104</b> the address of the range to select, or such means may also rely on a pointing device <b>105</b> mode where the user clicks on the range to select, or such means may rely on other similar ways not further described here. Then control is given to step <b>628.</b></li><li>At step <b>624,</b> a new record <b>401</b> is added to the Self-Replication table <b>400.</b> The <i>"PSRS Name"</i> field <b>402</b> is set equal to the value of the <i>"PSRS Name"</i> field <b>402</b> of the record <b>401</b> whose <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex,</i></b> the <i>"PSRR Address"</i> field <b>403</b> is set equal to the variable <b><i>NewRange,</i></b> the <i>"PSRR pattern"</i> field <b>404</b> is set equal to the value of the background pattern of the range with address <b><i>NewRange,</i></b> the <i>"Set Index"</i> field <b>405</b> is set equal to <b><i>PSRSindex,</i></b> the <i>"Range Index"</i> field <b>406</b> is set equal to the number of PSRR incremented by one. Then control is given to step <b>629.</b></li><li>At step <b>625,</b> the background pattern of the range with address equal to the value of the <i>"PSRR Address"</i> field <b>403</b> of the record <b>401</b> whose <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex</i></b> and whose <i>"Range Index"</i> field <b>406</b> is equal to <b><i>PSRRIndex</i></b> is set equal to the value of the <i>"PSRR pattern"</i> field <b>404</b> of the record <b>401</b> whose <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex</i></b> and whose <i>"Range Index"</i> field <b>406</b> is equal to <b><i>PSRRIndex.</i></b> Then control is given to step <b>630.</b> The <b><i>Persistent_Self_Replicate</i></b> method is no longer set as the routine handling the cell content modification event for the range of cells with address equal to the value of the <i>"PSRR Address"</i> field <b>403</b> of the record <b>401</b> whose <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex</i></b> and whose <i>"Range Index"</i> field <b>406</b> is equal to <b><i>PSRRIndex.</i></b></li><li>At step <b>626,</b> all the records <b>401</b> of the Self-Replication table <b>400</b> are deleted if their <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex.</i></b> Then the variable <b><i>PSRSIndex</i></b> is decremented , unless equal to zero. Then control is given to step <b>631.</b></li><li>At step <b>627,</b> a new record <b>401</b> is added to the Self-Replication table <b>400.</b> The <i>"PSRS Name"</i> field <b>402</b> is set equal to the variable <b><i>NewName,</i></b> the <i>"PSRR Address"</i> field <b>403</b> is left empty, the <i>"PSRR pattern"</i> field <b>404</b> is left empty, the <i>"Set Index"</i> field <b>405</b> is set equal to the number of PSRS incremented by one, and the <i>"Range Index"</i> field <b>406</b> is left empty. Then control is given to step <b>631.</b></li><li>At step <b>628,</b> the variable <b><i>NewRange</i></b> is set equal to the address of the range retrieved at step <b>623.</b> Then control is given to step <b>631.</b></li><li>At step <b>629,</b> the background pattern of the range of cells with address equal to <b><i>NewRange</i></b> takes the value PATTERN_PSRR. The <b><i>Persistent_Self_Replicate</i></b> method is set as the routine handling the cell content modification event for the range of cells with address equal to <b><i>NewRange.</i></b> Then control is given to step <b>631.</b></li><li>At step <b>630,</b> the record <b>401</b> in the Self-Replication table <b>400</b> with <i>"Set Index"</i> field <b>405</b> equal to <b><i>PSRSindex</i></b> and with <i>"Range Index"</i> field <b>406</b> equal to <b><i>PSRRindex,</i></b> is deleted. Then the variable <b><i>PSRRIndex</i></b> is decremented , unless equal to zero.</li><li>At step <b>631,</b> the Self-Replication Table <b>400</b> is first rearranged and then sorted so that the set of values taken by the <i>"Set Index"</i> fields <b>405</b> is contiguous and so that the sets of values taken by the <i>"Range Index"</i> field <b>406</b> for a given value of the <i>"Set Index"</i> field <b>405</b> are contiguous. Then the Self-replication Dialog Box <b>510</b> display fields are refreshed. The "Name" text box <b>511</b> is filled with the variable <b><i>NewName.</i></b> The "Existing PSRS" list box <b>512</b> is filled with the names found in the "<i>PSRS Name"</i> fields <b>402</b> of the various records <b>401</b> of the Self-Replicating Table <b>400.</b> Within the "Existing PSRS" list box <b>512,</b> the active item corresponds to the record <b>401</b> whose <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex.</i></b> The "Existing PSRR members" list box <b>513</b> is filled with the addresses found in the "<i>PSRR Address"</i> fields <b>403</b> of the various records <b>401</b> of the Self-Replicating Table <b>400</b> for which the <i>"Set Index"</i> field <b>405</b> is equal to <b><i>PSRSIndex.</i></b> Within the "Existing PSRR members" list box <b>513,</b> the active item corresponds to the record <b>401</b> whose "<i>Range Index"</i> field <b>406</b> is equal to <b><i>PSRRIndex.</i></b> The "Range" text box <b>514</b> is filled with the variable <b><i>NewRange.</i></b> Then control is given back to step <b>605</b> for waiting for a new user action to treat.</li></ul>

F.3. Persistent_Self_Replicate method

The method for automatically reflecting an update of the content of a PSRR onto the other PSRR belonging to the same PSRS to take advantage of the present invention is summarised in flowchart <b>700</b> of FIG <b>7.</b> This method can be seen as the processing of the <b><i>"Persistent_Self</i>_<i>Replicate"</i></b> command which is invoked each time the content of a PSRR is changed, as outlined in the step <b>629</b> of the <b><i>Self_Replication</i>_<i>Manager</i></b> method. The method comprises the following steps : <ul id="ul0010" list-style="bullet" compact="compact"><li>At step <b>701,</b> the method is in its default state, waiting for an event to initiate the process.</li><li>At step <b>702,</b> an event is detected, as a result of a PSRR content update.</li><li>At step <b>703,</b> the address of the updated PSRR, considered as a parameter of the <b><i>Persistent_Self_Replicate</i></b> command, is retrieved under the name CurrPSRR.</li><li>At step <b>704,</b> a regular copy operation is performed on the PSRR with address CurrPSRR.</li><li>At step <b>705,</b> the top record <b>410</b> of the Self-Replication Table <b>400</b> is set as the current record <b>401</b> of the table.</li><li>At step <b>706,</b> the "<i>PSRR Address"</i> field <b>403</b> of the current record <b>401</b> of the Self-Replication Table <b>400</b> is compared against CurrPSRR. If found equal, then control is given to step <b>707 ;</b> otherwise control is given to step <b>713.</b></li><li>At step <b>707,</b> the local variable CurrSetIndex is set equal to the value of the <i>"Set Index"</i> field <b>405</b> of the current record <b>401</b> of the Self-Replication Table <b>400.</b></li><li>At step <b>708,</b> the top record <b>410</b> of the Self-Replication Table <b>400</b> is set as the current record <b>401</b> of the table.</li><li>At step <b>709,</b> the <i>"Set Index"</i> field <b>405</b> of the current record <b>401</b> of the Self-Replication Table <b>400</b> is compared against CurrSetIndex. If found equal, then control is given to step <b>710 ;</b> otherwise control is given to step <b>711.</b></li><li>At step <b>710,</b> a regular paste operation is performed on the range of cells pointed by the <i>"PSRR Address"</i> field <b>403</b> of the current record <b>401</b> of the Self-Replication Table <b>400.</b></li><li>At step <b>711,</b> a test is performed to check if the current record <b>401</b> of the Persistent Copy-Paste Table <b>400</b> is in fact the last record of this table. If it is the case, then control is given to the initial step <b>701</b> for handling any new future command ; otherwise control is given to step <b>712.</b></li><li>At step <b>712,</b> the next record of the Persistent Copy-Paste Table <b>400</b> is set as the new current record <b>401</b> of this table. Then control is given to step <b>709.</b></li><li>At step <b>713,</b> a test is performed to check if the current record <b>401</b> of the Self-Replication Table <b>400</b> is in fact the last record of this table. If it is the case, then control is given to step <b>714;</b> otherwise control is given to step <b>715.</b></li><li>At step <b>714,</b> a "Should Not Occur" condition is logged as it is normally impossible not to find in the Self-Replication table <b>400</b> a record <b>401</b> with a <i>"PSRR Address"</i> field <b>403</b> equal to the parameter CurrPSRR of the command. Then control is given to step <b>701</b> for handing any new future command.</li><li>At step <b>715,</b> the next record of the Self-Replication Table <b>400</b> is set as the new current record <b>401</b> of this table. Then control is given to step <b>706.</b></li></ul>

<u>ALTERNATE EMBODIMENTS</u>

While the invention has been particularly shown and described with reference to a preferred embodiment, it will be understood that various changes in form and detail may be made therein without departing from the spirit, and scope of the invention.

The Persistently Self-Replication method and system according to the present invention may be used advantageously in those environments where elements of information are organised as multidimensional tables having more than three dimensions.