One of the benefits of using Smart Workpapers are the many features that can be added using formulae and coded actions specific to Smart Workpapers.
This article summaries some of the more complex formulas and coded actions available using the Smart Workpapers. For information on the basics of named ranges see the knowledge article: Customisations Using Named Ranges.
If you would like customisations made to your firm's workpapers, but don't feel confident doing them yourself, please refer to Business Fitness Customisation Service for more information on our team making the changes for you.
List of coded template named ranges
| Named Range | Action |
|---|---|
| Header information | |
| Tm_WorksheetTitle | Links the title of the worksheet to the name on the Index sheet. Used as the main header of the template (generally in cell B2) |
| Tm_Account | Links the active cell to the account details on the Index sheet. |
| Tm_Sub | Links the active cell to the sub account details on the Index sheet. |
| Tm_Title | Links to active cell to the template title. This will return the template name even if Tm_WorkSheetTitle changes through other functionality. |
| Go_Index | Creates a button which directs the user to the Index sheet. |
| Tm_Status | Monitors the status of the worksheet. |
| Go_Help | Creates a 'Help' button linking the workpapers to Business Fitness help articles. |
| Tm_Help | Archived NamedRange - DO NOT USE - You may see this in templates designed before 2018. Replaced with Go_Help. |
| Tick box/checklist items | |
| Go_TickBox | Toggles the cell between ticked and unticked (uses the Wingdings 2 font). |
| Go_Toggle_[*]_[*] | Toggles the cell been various values such as ticked, unticked and N/A (uses the Wingdings 2 font - Replace [*] with tick variables see details below). |
| Roll up (show/hide rows) | |
| Go_RollUp | Shows and hides rows using the named ranges Tm_StartRollUp and Tm_EndRollUp. |
| Tm_StartRollUp | Used for the RollUp function at the beginning of the rows to show/hide. |
| Tm_EndRollUp | Used for the RollUp function at the end the rows to show/hide. |
Go_ShowHideRows | Allows you to set a simple or complex display by showing and hiding rows (for an example see the 'Tax Rec' workpaper) Note: This formula is more complex and we recommend using the Business Fitness customisation service. |
| Go_ShowHideColumns | Allows you to set a simple or complex display by showing and hiding columns (for an example see the 'Tax Calc' workpaper). Note: This formula is more complex and we recommend using the Business Fitness customisation service. |
| Opening balance rollover | |
| FixedList | Copies information from last year's closing balance to the current year's opening balance by matching the text descriptions from one period to another. |
| DynamicList | Copies the list from the opening worksheet into the same location in the new worksheet. |
| DynamicOB | A list which will contain the data copied from the DynamicCB from the prior year worksheet into the opening balance of the current year worksheet. |
| DynamicCB | A list which will be copied into the DynamicOB range in the next year's worksheet. |
| SingleOB | A single cell which will copy the data from the SingleCB cell in the prior year as the current year opening balance. |
| SingleCB | A single cell which will copy data to the SingleOB cell in the next year. |
| Trial balance/workpaper reconciliation | |
| Tm_TBAccount | Pulls through the account name from the trial balance in a Connected Workpaper. |
| Tm_TBBalance | Pulls through the account balance from the trial balance in a Connected Workpaper. |
| Tm_ComparativeBalance | Pulls through the prior year account balance from the trial balance in a Connected Workpaper. |
| Reconcile_[namefield] | Activates the reconciliation field on the Index sheet of a Connected Workpaper. This can be added manually to the worksheet prior to being uploaded or added onto each template as required using the Add Reconciliation Field function on the Workpapers ribbon. |
| Checklist count | |
| Tm_ChecklistItems | Allows the Index sheet to count how many 'Checklist Items' on a worksheet template remain incomplete. |
| Hyperlink to named range in another worksheet | |
| Go_SumRangeDetail_[RangeName] | Hyperlinks you to the worksheet where the value is found. If there is more than one worksheet, it will provide a summary of all sheets for the given [RangeName] and their balances to confirm totals and make navigation easier. |
| Loan Calculations | |
| Go_CalculateLoan | Used Specifically for Finance Loan Calculations. Note: This formula is more complex and we recommend using the Business Fitness customisation service. |
| Index summary function | |
| Tm_CategoryList | Returns the name of the category from the worksheet. Note: This formula is more complex and we recommend using the Business Fitness customisation service. |
| CategoryNamesList | Entered below the headings of the summary data to be returned to the Index when Go_RefreshSummary is used. Note: This formula is more complex and we recommend using the Business Fitness customisation service. |
| Go_RefreshSummary | Refreshes the summary worksheet calculations used in workpapers such as FBT or Div 7A. Note: This formula is more complex and we recommend using the Business Fitness customisation service. |
Basic Coded Actions
'Tm_Status', 'Tm_WorksheetTitle', 'Tm_Account', etc.
These actions only use a named range to activate. Define the name of the cell that you would like the action to be visible (e.g. cell B2 for 'Tm_WorksheetTitle') and ensure the scope of the worksheet is set to the worksheet level.
For more information on creating named ranges see the knowledge article Customisations Using Named Ranges.
Using coded actions that begin with 'Go_'
For any of the coded actions that begin with 'Go_' ('Go_Index', 'Go_TickBox', 'Go_Toggle', 'Go_RollUp', etc), a hyperlink will need to be created in addition to the named range.
The following steps will assist in creating a named range and hyperlink:
1. Create the named range
Open the name manager from the formula tab in excel. For more information on named ranges see the knowledge article: Customisations Using Named Ranges.
Create a named range for the particular coded action (e.g. 'Go_Index').
Note: The named range is required to have a Scope specific to the worksheet being worked on and a Refers to of the desired cell/range on the worksheet.
Note: If there is more than one instance of the coded action in the template, be sure to create versions of the 'Go_' named range (e.g. 'Go_RollUp_01', 'Go_RollUp_02', etc.).
2. Create a hyperlink
Right-click on the cell you wish the coded action to be present in and select Link. To hyperlink, select Place in This Document and search through to find the named range you just created. Enter in text to display a screen tip showing the user what the hyperlink will do.
Note: For the action to work by clicking anywhere in the cell/s, rather than just the text hyperlinked, use the 'Wrap Text' option when formatting.
'Go_TickBox' and 'Go_Toggle'
'Go_TickBox' and 'Go_Toggle' are very similar to each other. An example of both can be found on our checklists. The top of the Index sheet contains 'Go_TickBox' and the Y/N/NA section in the individual worksheets contain 'Go_Toggle'.
Follow the hyperlink process outlined in the above 'Using coded actions that begin with Go_' section with some minor changes. Set the font of the cell/range to 'Wingdings 2'.
'Go_TickBox'
'Go_TickBox' is designed to have only two options: ticked and unticked.
'Go_Toggle_'
'Go_Toggle_' is designed to be customisable and you can have as many options as desired within the Wingdings 2 font. If you wanted additional characters, you just need to extend the named range (i.e. 'Go_Toggle_P_O_W_A_B_C_...'). Use a Wingdings 2 character chart to select the characters you require. 'Go_Toggle' allows you to have as many characters as you would like.
The following letters represent the characters in Wingdings 2:
- O = Cross
- P = Tick
- W= NA
Note: There is no 'empty' character. To achieve this, include a random letter at the end of the formula (e.g. '_S'). Use conditional formatting to change the colour of text when cell contains 'S' to be the same as the background colour. This will give the effect of an empty selection.
'Go_RollUp'
'Go_RollUp' allows you to show and hide cells and can be quite useful for long worksheets. There are two components to this coded action, the show/hide button, and the range of cells to be shown/hidden.
Note: It is very important to ensure the Scope for these named ranges is set to worksheet level in order to make the rollups work.
Show/Hide button
A Show/Hide button uses the named range 'Go_RollUp' and hyperlinks the desired cell to this named range. Follow the steps in the 'Coded actions that begin with Go_' section above. Then proceed with the 'Range of cells to be shown/hidden' instructions below.
Note: The hyperlink can be added to more than one cell in the row (i.e. you can create a button on the '+' and the section title).
Range of cells to be shown/hidden
In order to specify which cells are the start and finish of the RollUp, the named ranges 'Tm_StartRollUp' and 'Tm_EndRollUp' are used
Note: As there are normally multiple roll-ups on one worksheet, it is recommend adding numbers to the end (e.g. 'Tm_StartRollUp_01' and 'Tm_EndRollUp_01'. Ensure that the start and end named ranges have the same number.
The 'Tm_StartRollUp' should be the first row you want to show or hide, which will be the row under the show/hide button created above. In the example below, the 'Go_RollUp' show/hide button is in row 22 and the 'Tm_StartRollUp_01' is in row 23, directly after the 'Go_RollUp'.
Note: It does not matter which column you have the 'Tm_StartRollUp' in as it only applies to the rows not the columns.
In this example, 'Tm_EndRollUp_01' is in row 44 which is the last row for the roll-up.
Creating the expand/collapse (+/-) 'button'
To create a +/- option that changes according to whether the section is expanded or collapsed, you need to enter the following formula into the relevant cell (typically the same cell with the 'Go_RollUp' action):
=IF(SUBTOTAL(103,[cell]),"-","+")
For the formula to work, you need to reference a cell that contains data. In the example below, a space has been added to cell A31.
'Go_SumRangeDetail_[RangeName]'
The 'Go_SumRangeDetail' creates a link to the worksheet where the value is found. If there is more than one worksheet, it will provide a summary of all sheets for the given [RangeName] and their balances to confirm totals and make navigation easier.
In the formula 'Go_SumRangeDetail_[RangeName], the [RangeName] refers to the specified named range.
For example, in the image below, a named range has been created to link the 'C20 Income Tax Rec' worksheet to the July instalment. The July instalment has the named range 'J10_JulInst'.
Follow the steps in the 'Coded actions that begin with Go_' section above to hyperlink the cell and create the named range. When creating the named range, replace [RangeName] with the July instalment named range. (i.e. the named range would be 'Go_SumRangeDetail_J10_JulInst')
FixedList, DynamicList, DynamicOB, DynamicCB, SingleOB and SingleCB
These named ranges apply to the import opening balance function. For more information please refer to the knowledge article: Customising Opening Balances.
'Tm_TBAccount', 'Tm_TBBalance' and 'Reconcile_[namefield]'
- 'Tm_TBAccount', 'Tm_Balance', 'Tm_TBComparativeBalance': Allow a template to obtain information from the trial balance of a connected file.
- 'Reconcile_[namefield]': Allows the index to obtain the reconciliation status of a template.
Configure the template workpaper to have the following defined named ranges (no hyperlinks are needed):
- Account Name: 'Tm_TBAccount'
- Account Balance: 'Tm_TBBalance'
- Account Comparative Balance: 'Tm_TBComparativeBalance'
- Reconciliation Field: 'Reconcile_[namefield]'
Note: If there is more than one reconciliation, as is the case in the 'F10 Bank' worksheet, append a number to pair them together in series. For example: "Tm_ TBAccount _1" and " Tm_TBBalance _1", "Tm_TBAccount_2" and "Tm_ Tm_TBBalance _2" etc.
Template customisation service
If you would like customisations made to your firm's workpapers, but don't feel confident doing them yourself, please refer to Business Fitness Customisation Service for more information on our team making the changes for you.