Connected Workpapers - Referencing and Reconciliation

Cross Referencing

Connected workpapers allow two-way referencing between the trial balance and your Excel workpapers. 

    A Reconciliation Field is added to a cell in an individual workpaper and allows the 'Index' worksheet to reconcile that field (generally a closing balance or a summed total amount). 

    A Reference Formula is added to a cell in an individual workpaper and imports the value from the trial balance into the workpaper. 

Add a Reconciliation Field

To add a reconciliation field, go to the workpapers tab in the excel ribbon while on the workpaper you want to add a reconciliation field to.  Select the cell that you wish to reconcile the trial balance to and click on the "Add Reconciliation Field" button.  

A pop-up box will appear.  Enter a name for this reconciliation field and select 'OK'.  

Return to the index, and on the line for the workpaper the reconciliation field was added to, click in the "Reconcile To" column.  A drop-down box will appear.  Select the reconciliation field created for this worksheet in the previous step.

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.

Add a Reference Formula

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

To add a Reference Formula, go to the 'Index' tab select the cell with the value in the trial balance to be referenced (It is also possible to select an account number or name)

Navigate to the workpapers tab in the excel ribbon, while keeping the cell selected, click on the "Add Reference Formula" button.  A pop-up box will appear, select 'OK'.  

Navigate to the workpaper and specific cell that you would like to link to the value from the trial balance, and right-click Paste or Ctrl-V to paste the newly created formula into that cell. 

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

Note: A new reference formula needs to be created for every cell you wish to reference.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.