One of the benefits of using Smart Workpapers are the many features that can be added using formulas and coded actions specific to Smart Workpapers. The article Customisations using named ranges covers the basic things you need to know about named ranges and customising the Smart Workpapers. For some of the more complex formulas and coded actions available using the Smart Workpapers, please read the article below.

If you would like customisations made to your firms 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.

The table below contains a list of the named ranges used for coded actions.


Tm_Status

Monitors the status of the worksheet.
Tm_WorksheetTitle
Links the title of the worksheet to the name on the index page.
Go_Index
Links to the index page.    
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).
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_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.
FixedList
Copies information from last years closing balance to the current years 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.
Go_ShowHideRows
Allows you set a simple or complex display by showing and hiding rows (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).


Tm_Status and Tm_WorksheetTitle


These are the two simplest coded actions to use. Define the name in the cells where you would like the status to show for Tm_Status (generally in cell B2 for the Tm_WorksheetTitle) and ensure the scope of the worksheet is set to the worksheet level.


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), a hyperlink will need to be created. The following steps will assist in creating the hyperlink.


  1. To create a hyperlink, you will first need to create a named range for that particular coded action (e.g Go_Index).  At this point, create the named range pointing to any cell on the worksheet.  
  2. Right-click and select Link. To hyperlink, select Place in This Document and search through to find the named range you just created. Enter in the text to display and also a screen tip showing the user what the hyperlink will do.
  3. You will notice that when you click on the hyperlink, it will take you to the cell you created as a hyperlink. To make this work correctly, we will need to change the named range to the following formula               =INDIRECT(CurrentCell())   

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 index section at the top contains Go_TickBox and the Y/N/NA section in the body contains Go_Toggle.

The hyperlink process outlined above is required with a few minor changes. The Go_TickBox and Go_Toggle are designed to be the font Wingdings 2 (or using the Wingdings 2 style in the Business Fitness workpapers)

The following letters represent the characters in Wingdings 2:

  • O = Cross
  • P = Tick
  • W= NA

If you wanted additional characters to toggle using Go_Toggle, we would suggest searching for a Wingdings 2 character chart. Go_Toggle allows you to have as many characters as you would like.


Go_RollUp


Go_RollUp allows you to show and hide cells and can be quite useful for long worksheets. To start with, follow steps 1-2 above using the Go_RollUp function. In order to specify which cells are the start and finish of the RollUp, you will need to use the Tm_StartRollUp and Tm_EndRollUp named ranges. As there are normally multiple on one worksheet, we recommend adding numbers to the end, eg Tm_StartRollUp_01 and Tm_StartRollUp_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 hyperlink.  In the example below, the Go_RollUp hyperlink is in row 403.  It does not matter which column you have the Tm_StartRollUp in as it only applies to the rows.  In the example below, Tm_StartRollUp_43 is in row 404.   It is very important to ensure the scope for these named ranges is set to worksheet level in order to make the rollups work.

In this example, the last row for the roll-up is 408, so this is where the Tm_EndRollUp named range is found.

Once you have entered all of the Tm_StartRollUp and Tm_EndRollUp named ranges, move on to step 3 above, changing the named range for the cell of the Go_RollUp to the formula =INDIRECT(CurrentCell())


Go_SumRangeDetail_[RangeName]


The Go_SumRangeDetail will take you either to the worksheet where the named range can be found, or open a pop-up box with a summary of all worksheets with that named range. In the formula, the [RangeName] refers to the specified named range. We will use the Provision for Income Tax worksheet as an example. To link to the July Instalment, go to that worksheet and find the named range. In this example, the July Instalment has the named range J10_JulInst. Create the above formula, link to any cell on the worksheet, and replace [RangeName] with the July instalment named range. So the formula in this example would be Go_SumRangeDetail_J10_JulInst. Follow steps 1-3 above to hyperlink the cell and create the named range.


FixedList, DynamicList, DynamicOB, DynamicCB, SingleOB and SingleCB


These named ranges apply to the import opening balance function. For more information please refer to the article Customising Opening Balances.