Cross Referencing

Connected workpapers allow two-way referencing between the trial balance and your Excel workpapers. A Reconciliation Field lets the trial balance (on the Index worksheet) know about a figure in your Excel workpaper. A Reference Formula lets your workpaper know about a value on the trial balance.

Reconciliation Field

On any given worksheet, you can select a field that you wish to reconcile by comparing it to the relevant balance of the account in the index. To activate this feature, select a cell in the worksheet that you wish to reconcile to, and from the Workpapers tab in Excel, select Add Reconciliation Field. This will prompt you for a name, which is a brief identifier so you can select from potentially more than one Reconciliation Field in the Index.

Once saved, return to the index, where if you select the cell against the workpaper record, any Reconciliation Fields that are on the underlying worksheet will appear in drop down list. Once selected, Reportance will compare the balance of the account to the value of the Reconciliation Field. If the values disagree, the Go to Workpaper button will go red in colour. If the balances agree, or if the value of the Reconciliation Field is 0, the button will go green in colour.

Reference Formula

Normally,in your excel workpaper you could simply create a formula and reference a value from the trial balance. However, these formulas will break when you reload the workpaper as this will destroy the references.

To create a reference to a trial balance item in your excel workpaper, use the following steps instead:

1) Go to the index worksheet and select the cell with the value on the trial balance that you would like to reference (you can also select the account number or name if you wish to reference these).

2) In the Workpapers tab in Excel, select the Reference Formula button. This creates a custom formula and copies it to your clipboard.

3) Go to your workpaper to the cell that you would like to include the value from the trial balance, and right-click Paste or press Ctrl-V to paste the newly created formula into that cell.

This reference formula will continue to reference the correct value on the trial balance even after a refresh.