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.