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. 


For information on the basics of named ranges see the knowledge article Using Named Ranges - Customisations.


This article summaries some of the more complex formulas and coded actions available using the Smart Workpapers.


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 RangeAction
Header Information
Tm_WorksheetTitle
Links the title of the worksheet to the name on the index page. Used as the main header of the template (generally in B2)
Tm_AccountLinks the active cell to the account details on the index page.
Tm_SubLinks the active cell to the sub account details on the index page.
Tm_TitleLinks to active cell to the template title. This will return the template name even if Tm_WorkSheetTitle changes through other functionality.
Go_IndexCreates a button which directs the user to the index page.
Tm_StatusMonitors the status of the worksheet.
Go_HelpCreates 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_TickBoxToggles 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_StartRollUpUsed for the RollUp function at the beginning of the rows to show/hide.
Tm_EndRollUpUsed for the RollUp function at the end the rows to show/hide.

Go_ShowHideRows
Allows you set a simple or complex display by showing and hiding rows (i.e. an example see Tax Rec Workpaper - note: this formula is more complex and we recommend using the Business Fitness customisation service).


Opening Balance Rollover 
FixedListCopies information from last year's closing balance to the current year's opening balance by matching the text descriptions from one period to another.
DynamicListCopies the list from the opening worksheet into the same location in the new worksheet.
DynamicOBA list which will contain the data copied from the DynamicCB from the prior year worksheet into the opening balance of the current year worksheet.
DynamicCBA list which will be copied into the DynamicOB range in the next year's worksheet.
SingleOBA single cell which will copy the data from the SingleCB cell in the prior year as the current year opening balance.
SingleCBA 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. Must be used with Tm_TBBalance
Tm_TBBalance
Pulls through the account balance from the trial balance in a Connected Workpaper. Must be used with Tm_TBAccount
Tm_ComparativeBalancePulls through the prior year account balance from the trial balance in a Connected Workpaper. must be used with Tm_TBAccount and Tm_TBBalance
Reconcile_[namefield]
Activates the reconciliation field on the index page 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_ChecklistItemsAllows index page 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_CalculateLoanUsed Specifically for Finance Loan Calculations (note: this formula is more complex and we recommend using the Business Fitness customisation service).


Index Summary Function
Tm_CategoryListReturns the name of the category from the worksheet (note: this formula is more complex and we recommend using the Business Fitness customisation service).
CategoryNamesListEntered 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 where you would like the action to be visible (for example: generally, in cell B2 for the 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 Using Named Ranges - Customisations.



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. 


Create Named Range

Open the name manager from the formula tab in excel. For more information on named ranges see the knowledge article Using Named Ranges - Customisations.


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 

The named range is required to refers to the desired cell/range on the worksheet. (If the coded action is present more than once in a template select the first instance)



Create 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 the text to display a screentip showing the user what the hyperlink will do.



If there is only one instance of the coded action in the template this is all that is required. 


Edit Named Range 

Ensure that the workbook has all instances of the Hyperlink set before proceeding to this step.

When you click on the hyperlink, it will redirect to the first instance and not work for any other instance. 

To make this work correctly, edit the named range 'Refers to:' section 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.


Follow the hyperlink Process outlined in the section Using coded actions that begin with Go_ with some minor changes


Set the font of the cell/range to 'Wingdings 2' - The Go_TickBox and Go_Toggle are designed to be the font Wingdings 2.


Go_TickBox

Go_Box is designed to have only two options: ticked and unticked. 


Go_Toggle_

Go_Toggle_ is designed to be customizable to the designer and can have as many options as desired within the Wingdings 2 font:

If you wanted additional characters to Go_Toggle, this can be achieved by extending the named range (i.e. Go_Toggle_P_O_W_A_B_C......)

It is suggested searching for a Wingdings 2 character chart. 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. '_P').  Use conditional formatting to change the colour of text when cell contains 'P' 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 steps outlined in coded actions that begin with Go_ instructions. Pause after creating the hyperlink and proceed to the Range of Cells to be Shown/Hidden instructions.


Note: The hyperlink can be added to more 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 on one worksheet, it is recommend adding numbers to the end, e.g. 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 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


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.


Once you have entered all of the Tm_StartRollUp and Tm_EndRollUp named ranges, return to the instructions coded actions that begin with Go_, 'Edit Named Range'  for the cell of the Go_RollUp to the formula =INDIRECT(CurrentCell())



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, the [RangeName] refers to the specified named range. 


In the below example a named range has been created to link the Provision for Income Tax worksheet to the July Instalment.  

The July Instalment has the named range J10_JulInst. 


Follow steps in the instructions coded actions that begin with Go_ above to hyperlink the cell and create the named range.

Create the named range and 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 article Customising Opening Balances.



Tm_TBAccount, Tm_TBBalance & 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, for example, the 'bank rec', 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.




HowNow 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.