System and method for dynamic data access in 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 spreadsheet, the method comprising:
 defining a parameter associated with the spreadsheet; and   generating a data query based at least in part on the parameter.   
     
     
         2 . The method of  claim 1 , further comprising:
 generating an output based at least in part on the results of the data query.   
     
     
         3 . A method for utilizing a spreadsheet, the method comprising:
 defining a template within the spreadsheet; and   generating an output based at least in part on the template.   
     
     
         4 . The method of  claim 3 , further comprising:
 generating a data query based at least in part on a parameter associated with the spreadsheet.   
     
     
         5 . The method of  claim 3  wherein one or more cells within the template contain formulas. 
     
     
         6 . The method of  claim 5  wherein the formulas are written in spreadsheet formula language. 
     
     
         7 . The method of  claim 3  wherein generating an output further comprises:
 replicating one or more cells within the template. 
 
     
     
         8 . The method of  claim 7  wherein replicating one or more cells further comprises:
 preserving relative cell references. 
 
     
     
         9 . The method of  claim 7  wherein replicating one or more cells further comprises:
 replicating formatting of the template cells. 
 
     
     
         10 . The method of  claim 7  wherein replicating a cell further comprises:
 associating values from a data query with the one or more replicated cells by using column names in formulas within the one or more replicated cells; and 
 performing calculations using the associated values. 
 
     
     
         11 . The method of  claim 10 , further comprising:
 performing special processing on the output when the data query returns no associated values.   
     
     
         12 . The method of  claim 10 , further comprising:
 automatically sorting the output based at least in part on the associated values of the one or more cells in the output.   
     
     
         13 . The method of  claim 3 , further comprising:
 associating a formula language name with the output.   
     
     
         14 . The method of  claim 3 , further comprising:
 automatically updating the output when a change is detected.   
     
     
         15 . The method of  claim 14 , wherein the change comprises a change to:
 i) template cell formulas   ii) template cell formatting   iii) template cell values, or   iv) data query parameters.   
     
     
         16 . A method for utilizing a spreadsheet, the method comprising:
 defining an output range within the spreadsheet;   rendering the output range; and   allowing a user to modify the rendered output range.   
     
     
         17 . The method of  claim 16 , further comprising:
 rendering the output range using HTML.   
     
     
         18 . The method of  claim 16 , wherein allowing a user to modify the rendered output range further comprises:
 allowing the user to sort columns within the output range using a user input.   
     
     
         19 . The method of  claim 16 , wherein allowing a user to modify the rendered output range further comprises:
 allowing a user to interactively expand and collapse a hierarchy using a user input.