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.