Connected Workpapers allow two-way referencing between the Index Page and your Excel worksheets.
- A 'Reconciliation Field' is added to a cell in an individual worksheet that allows the Index Page to reconcile to that field (generally a closing balance or a summed total amount).
- A 'Reference Formula' is added to a cell in an individual worksheet and imports the value from the Index Page into that worksheet.
Adding a reconciliation field
To add a reconciliation field, go to the Workpapers tab in the Excel ribbon in the worksheet you want to add the reconciliation field to. Select the relevant cell and click on the Add Reconciliation Field button.
A dialogue will appear. Enter a name for this reconciliation field and click OK. Return to the Index, and on the row of the relevant worksheet click in the Reconcile To column. A dropdown menu will appear. Select the reconciliation field created for this worksheet.
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 (in the Links column) will go red in colour. If the balances agree, the button will go green in colour.
Note: You can add multiple reconciliation fields to the one worksheet.
Adding a reference formula
Normally, in an excel workpaper you could simply create a formula and reference a value in the Index (by using the = function). However, this formula will break when you reload the Index. This is because the reload function strips and recreates the entire trial balance in the Index, which in turn destroys any references. To overcome this, you will need to create a reference formula.
To add a reference formula, go to the Index and select the relevant cell to be referenced (e.g. the balance of an account). Then navigate to the Workpapers tab in the Excel ribbon and click on the Add Reference Formula button. A dialogue will appear alerting you to the fact that the reference formula has been copied, select OK.
Navigate to the relevant worksheet and specific cell that you would like the value to link to, and right-click Paste (or Ctrl-V) to paste the newly created formula into that cell.
This formula (a v-lookup) will continue to reference the correct value on the Index Page even after a trial balance refresh.
Note: A new reference formula needs to be created for every cell you wish to reference.
Go to source
Various worksheets allow you to inspect a balance calculation or go directly to the source worksheet. This allows you to identify the origin of a figure from another worksheet easily.
To use this function, click on the blue arrow located next to the figure in a worksheet.
Depending on the balance, there may a number of worksheets linked making up the resultant balance:
- No worksheets: If no worksheet has been inserted into the file which the amount relies on, a blank popup box will appear.
- One worksheet: If there is just one worksheet in relation to the amount, clicking on the arrow will take you to the amount and show you which worksheet the amount originated from.
- Multiple worksheets: If there are multiple worksheets in relation to the amount, a popup will appear and show you what the amount is comprised of. On this popup, you will have the option to go to the worksheet.
