Formula Recalculation - Important Information |
 
|
Formula Recalculation - Important Information |
|
Microsoft Excel recalculates formulas only when the cells that the formula depends on have changed. This aspect of the program is very significant in the context of the Flexcom Excel Add-In. If you are using an Excel workbook to retrieve analysis results directly from a Flexcom database, the values presented in the spreadsheet will not be automatically updated if you re-run the analysis which created the database.
The Flexcom Excel Add-In is based on the use of functions (as opposed to macros) to retrieve information from the database files. Even the standard templates provided on the Flexcom ribbon control are based on a pre-defined body of functions. When you insert a function into a cell for the first time, the returned value is naturally correct. However if the database contents changes subsequently, the cell contents are not automatically updated.
The use of either of the following procedures is recommended to ensure your worksheet is up to date:
1.Fill a cell with the complete directory, including the filename, of the database you wish to interrogate.
2.In a nearby cell, insert the function ‘UpdateHeaderIfChanged’. Its argument will be the cell containing the filename, as shown in Step 1 (i.e. cell A2). This function checks to see if the database contents have changed since it was loaded into Excel.
3.Any subsequent functions which use the database name as an argument should reference the cell created in Step 2 (i.e. cell A5).
4.To update your workbook, select the cell created in Step 1 (i.e. A2). Place the cursor at the end of the function in the formula bar (as shown below). Press the Return key to manually force Microsoft Excel to update the cell contents.
Steps 1 to 4 should be repeated for any and all subsequent databases you wish to interrogate. If these steps are not followed, it is possible that erroneous data may be displayed.
1.Reserve some governing cells for the storage of the names of the database files which your workbook references (as shown below). All cells within the workbook should reference one of these master cells, either directly or indirectly.
2.Save your workbook and close Microsoft Excel.
3.Launch Microsoft Excel and reopen your workbook.
4.Select the first cell in the master list of database files. Place the cursor at the end of the file name in the formula bar (as shown below). Press the Return key to manually force Microsoft Excel to update the cell contents. Repeat this procedure for every other cell in the master database list.
This may sound like a tedious procedure, but as long as you follow the guidelines above, it is a very reliable method and relatively quick to employ. The operation of Microsoft Excel in this regard is outside our control. Note also that if you are comfortable with the use of macros or Visual Basic scripts, it should be possible to write an algorithm which performs the updates automatically.