Named ranges can be used to create a link between individual Excel worksheets by referencing a range from within another worksheet. This is particularly useful when templates are being prepared in separate files.
A 'named range' is a way of referencing a cell (i.e. A1) or a range of cells (i.e. A1:B6) by giving it a name.
Named range training example
The following video briefly explains how to add a formula to automatically insert an amount from one worksheet to another worksheet. The example uses the workpaper template 'Fines and Penalties' and inserts the total value into the workpaper template 'Tax Reconciliation'.
Accessing named ranges
You can access named ranges in Excel by going to the Formulas ribbon and clicking on Name Manager.
The name of a cell/range can also be seen on the left-hand side of the formula bar when the cell/range is selected.
Adding a named range to a cell
To create a cell that can be used in calculations by other worksheets, you can either use the New button using the Name Manager, or right-click on the cell/range and select Define Name, then set the name.
Naming convention of named ranges
We recommend using a consistent naming convention in the following format: TemplateName_FieldName (Example: TradeDebtors_ClientListing)
Note: That the name is by convention only, the formula will work with any unique value.
Here are some rules for naming 'Named Ranges' in Excel:
Do's:
- The only symbols valid in range names are the period (.), question mark (?), underscore (_), and backslash (\) symbols, as long as they are not used as the first character of the name
You can use any single letter as a range name except for R and C. These are reserved in Excel for the R1C1 reference style
Don'ts:
- Don't use operator symbols (+, –, *, /, <, >, &) in range names
- Don't use names that Excel uses internally (Auto_Activate, Auto_Close, Auto_Deactivate, Auto_Open, Consolidate_Area, Criteria, Data_Form, Database, Extract, FilterDatabase, Print_Area, Print_Titles, Recorder, and Sheet_Title)
Scope
The Scope should always be set to the name of the worksheet. Avoid the use of 'Workbook' level named ranges. The workpaper system enables you to import a template more than once, so 'Workbook' level named ranges frequently give rise to naming conflicts. The 'Workbook' level named range is generally reserved for worksheets that will become part of a starter file.
Using a named range in a formula
There are two formulae we use to insert a named range which are specific to the smart workpapers add-in:
1. NamedRange - Use this for formulae linking to the starter file (i.e. linking the client name on the worksheet to the homepage of the starter file)
=NamedRange("Cl_Name","Client Name")
2. SumNamedRange - Use this for formulae linking to other worksheets
=SumNamedRange("named range of cell you wish to insert")
Note: The "" around the name of the named range must be present for the formula to work.
For more detailed instructions on some of the specific functions that are used in the smart workpapers, see the knowledge article: Formulae and Coded Actions.
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.