System and method for dynamic binding of a spreadsheet with external parameters

Abstract

In one aspect, the present invention relates to utilizing a spreadsheet by defining a parameter external to the spreadsheet and associating the parameter with the spreadsheet to define a parameterized workbook. In one embodiment, this utilization further comprises storing a location of the spreadsheet and storing the name of the parameter in the same storage module as the location. In another embodiment, it includes defining a result external to the spreadsheet, the result referencing one or more cells within the spreadsheet. in another embodiment, this utilization further comprises receiving a value for the parameter and generating the result based at least in part on the value and the spreadsheet. In another embodiment, it further comprises associating a type with the parameter. The type can define a range of values or attributes associated with the parameter.

1 . A method for utilizing a plurality of spreadsheets, the method comprising:
 defining a parameter external to the spreadsheets;   associating the parameter with the plurality of spreadsheets at design time to define a virtual workbook;   defining a set of one or more selection rules at design time to select a target spreadsheet from the plurality of spreadsheets based at least in part on the value of the parameter;   receiving a value for the parameter at run time;   choosing a target spreadsheet based on the selection rules and the value of the parameter;   computing the cell values in the target spreadsheet; and   rendering an output based on the computed cell values.   
     
     
         2 . The method of  claim 1 , further comprising:
 associating the external parameter with a spreadsheet in the plurality of spreadsheets, wherein the parameter applies to the spreadsheet as a whole, thereby allowing any formula in the spreadsheet to reference the parameter; and   when the spreadsheet is the target spreadsheet, computing the cell values in the target spreadsheet that are dependent, directly or indirectly, on the parameter.   
     
     
         3 . The method of  claim 1 , further comprising:
 defining a named result external to the plurality of spreadsheets, wherein the named result references one or more cells within the target spreadsheet.   
     
     
         4 . The method of  claim 3 , further comprising:
 generating the named result based at least in part on the received parameter value and the target spreadsheet.   
     
     
         5 . The method of  claim 4 , further comprising:
 defining a format for the named result.   
     
     
         6 . The method of  claim 1 , further comprising:
 associating a type with the parameter at design time, wherein the type defines a range of values that are allowable to be received for the parameter at run time.   
     
     
         7 . The method of  claim 1 , further comprising:
 associating a type with the parameter at design time, wherein the type defines attributes associated with the parameter, wherein the attributes are allowable to be used by the selection rules.   
     
     
         8 . The method of  claim 2 , further comprising:
 associating a type with the parameter at design time, wherein the type defines attributes associated with the parameter, wherein the attributes are allowable to be used by formulas within the target spreadsheet.   
     
     
         9 . The method of  claim 2 , further comprising:
 storing, separate from the virtual workbook and the target spreadsheet and after the target spreadsheet closes, an intermediate value used in a calculation for the target spreadsheet; and   associating the intermediate value with the virtual workbook and a value of the parameter used to calculate the intermediate value.   
     
     
         10 . The method of  claim 9 , further comprising:
 calculating a named result of the virtual workbook based at least in part by using the stored intermediate value instead of calculating the formulas for some of the cells in the target spreadsheet.   
     
     
         11 . The method of  claim 10 , further comprising:
 automatically calculating a named result associated with the virtual workbook without opening the target spreadsheet by using the stored intermediate value.   
     
     
         12 . The method of  claim 11 , wherein the stored intermediate value is the value of the named result. 
     
     
         13 . The method of  claim 1 , further comprising:
 distributing the calculations of the target spreadsheet among a plurality of computing devices.   
     
     
         14 . The method of  claim 13 , wherein distributing the calculations of the target spreadsheet comprises:
 distributing the calculations of the target spreadsheet among a plurality of computing devices based at least in part on the selection rules or on one or more formulas in the target spreadsheet.   
     
     
         15 . The method of  claim 14 , wherein distributing the calculations of the target spreadsheet comprises:
 distributing the calculations of the target spreadsheet among a plurality of computing devices based at least in part on one or more associations stored in a database associated with the virtual workbook.   
     
     
         16 . A method for utilizing a virtual workbook of  claim 1 , the method comprising:
 defining a name for a virtual workbook, wherein the name refers to the plurality of spreadsheets and the selection rules associated with them;   using a formula in a spreadsheet to refer to the virtual workbook by name;   supplying a value for the parameter to the virtual workbook in the formula; and   using a calculated output of the target spreadsheet of the virtual workbook as the result of the formula.   
     
     
         17 . The method of  claim 16 , further comprising:
 supplying the name of a named result of the virtual workbook in the formula and using the calculated value of the named result as the result of the formula.   
     
     
         18 . A system for utilizing a plurality of spreadsheets, comprising:
 a list of external parameters; and   a selection module to select a target spreadsheet from a plurality of spreadsheets based on a set of selection rules defined on the plurality of spreadsheets and the external parameter list.   
     
     
         19 . A system of  claim 18 , further comprising:
 a calculation module to receive a value for an external parameter and a target spreadsheet and generate an output based at least in part on the target spreadsheet and the value of the parameter.   
     
     
         20 . A system of  claim 19 , further comprising:
 an instantiator module to calculate a named result value based on an external parameter value, the selection rules, the plurality of spreadsheets and one or more cells designated as the named result.   
     
     
         21 . A system of  claim 19 , further comprising:
 a referencing spreadsheet having one or more formulas that refers to a virtual workbook by name and supply a value for the virtual workbook parameters, wherein the value of the formula is an output calculated from the target spreadsheet of the virtual workbook based on the parameter values.   
     
     
         22 . A system of  claim 19 , further comprising:
 a storage module to store a location of each of the spreadsheets in the plurality of spreadsheets, the list of selection rules, the name of each external parameter and the list of named results.