Calculating
The calculation buttons allow you to control exactly what cells are refreshed. They are used in conjunction with the refresh mode option (on/off).
Refresh Range, Sheet, Workbook or All
To calculate a range of cells simply:
- Highlight the range.
- Or select multiple ranges using the ctrl button (as normal in Excel).
- Or select a range and then select multiple Excel worksheets to calculate the same range on each sheet.
- Click the “Range” refresh button.
Please note, do not use the range refresh option to recalculate the entire sheet i.e. by selecting all the cells on the sheet. This will take unnecessarily long. Rather use the sheet refresh option.
If you click range refresh while on a cell which is the input value for a GL Wand function, those functions will also calculate.
To calculate a sheet simply:
- Select the applicable sheet.
- Or select multiple sheets to calculate each of the selected sheets.
- Click the “Sheet” refresh button.
To calculate a workbook simply:
- Select the applicable workbook.
- Click the “Workbook” refresh button.
To calculate all open workbooks simply:
- Click the “All” refresh button
Short cut keys are available to start a calculation using the keyboard. Use Ctrl-Alt-R for a range calculation and similarly you can use “S”, “W” and “A” to trigger calculations on the sheet, workbook and all open workbooks.
Refresh Mode on or off
You can use the toggle button to control when requests for calculations are sent to the server. If you toggle GL Wand refresh mode to “Off”, calculation requests are not sent to the server which means you:
- Can manipulate a worksheet.
- Change cell values.
- Enter new functions.
- And perform any other task that would normally cause Excel to recalculate.
This is useful during report development. To calculate the results simply click one of the refresh buttons.
If you leave the GL Wand refresh mode “On”, the applicable functions will be recalculated whenever Excel initiates a calculation process e.g.
- When changing the contents of the cell.
- Or changing one of the function’s input values.
Please note that this refresh mode option has nothing to do with Excel’s automatic and manual calculation. This option controls only the calculation of the applicable GL Wand functions. All other calculations are controlled as normal by the Excel automatic and manual calculation option.
Calculation progress and stopping a calculation
When you perform a recalculation a window will appear to show the progress of the calculation and to allow to you stop the calculation.
Showing the last calculated value
You can control if the cells show their last calculated value or a pending status. See the GL Wand User Options section. This option is useful to display the last balance that was calculated the next time the workbook is opened. There are also many actions which trigger a calculation in Excel and this option can be used to retain the last calculated balances in the cells when one of these actions occurs. Use this option along with the Refresh Mode to set the calculation behavior that you prefer.
Depending on your selected options the Excel cells may not contain the latest balances from your ERP system. Always use the refresh buttons to calculate the formulas to ensure they have the latest balances.
In-Memory Database
The in-memory database can be enabled under the GL Wand User Options. With this option enabled, GL Wand keeps track of frequently used balances and returns them directly to Excel if they are still current. This results in fewer queries being sent to the Oracle database for balances that have not changed and much faster performance getting these balances into Excel. Some important points about the in-memory database:
- The balances are stored in memory on the client PC. The PC should therefore have adequate memory available. There is a setting which your system administrator can control that determines how much PC memoryis made available to GL Wand.If you start seeing a warning in GL Wand that memory is running low, contact your system administrator to have this setting modified.
- Should you need to clear the in-memory database, this can be done under the GL Wand User Options.
- The in-memory database is saved to a file on the local PC when you close GL Wand and loaded again the next time you launch.
- There is a further setting under the GL Wand User Options which allows you to control the maximum time in days that a balance should be retained in the in-memory database if it is not accessed. This setting can also assist to manage the amount of memory used.