Named Ranges can be used to create a link between each individual worksheet, to reference a range from within another worksheet, excel 'named ranges' can be used.
This is particularly useful when the 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 Rage 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 templates 'Fines and Penalties' and inserts the total value into the workpaper template 'Tax Reconciliation'.
Accessing named ranges
You can access the Name Manager in excel by going to the 'Formulas' tab 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, a user can either use the add using the name manager above, 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:
- 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'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).
The scope should always be set to the name of the worksheet. Avoid the use of 'workbook' level named ranges.
The workpaper system enables a user to import a template more than once, workbook level named ranges frequently give rise to naming conflicts. The 'workbook' level named range is generally reserved for worksheets that will become a part of the starter file.
Using a Named Range in a Formula
There are two formulas we use to insert a named range, which are specific to the smart workpapers add-in:
NamedRange - Use this for formulas linking to the starter file (i.e. linking the client name on the worksheet to the homepage of the starter file)
SumNamedRange - Use this for formulas linking to other worksheets
=SumNamedRange("named range of cell you wish to insert")
Note: The "" around the name of the named range. These must be present for the formula to work.
Other functions using the smart workpaper add-in
For more detailed instructions on some of the specific functions that are used in the smart workpapers, please refer to the article Formulas and Coded Actions.