Overview: 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.
Please refer to the Glossary for definitions of key terms used in this article.
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 or cell range can also be seen on the left-hand side of the formula bar when the cell or 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 have two options. You can use the New button using the Name Manager, as below.
Alternatively, you can right-click on the cell or select the range and select Define Name.
Note: In order to add a named range to a Business Fitness worksheet, you would need to unprotect the worksheet. This is not recommended as this could unintentionally break the existing functionality of the worksheet.
WARNING: If a named range is added to a worksheet, that named range only exists within that worksheet in that specific workbook / binder. When the binder is rolled over or a new binder is opened, the named ranges that were created won't exist.
To ensure the named ranges persist across rollovers or into other workbooks / binders, the admin owner will need to upload the worksheet as a customised firm record template so the named ranges remain in the template. For admin users, refer to the following support article for more information: How to add a firm record template.
Details
Once you have clicked to add a new named range, the below pop-up will appear.
Name
Naming convention of named ranges
It is recommend to use a consistent naming convention in the following format: TemplateName_FieldName. For example, TradeDebtors_ClientListing.
Note: The name is by convention only, the formula will work with any unique value.
Rules for naming named ranges in Excel
DO'S | DONT'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. | 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). |
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. |
Using a named range in a formula
There are two formulae we use to insert a named range which are specific to the Active Workpapers add-in:
NamedRange
Use this for formulae linking to the binder (i.e. linking the client name on the worksheet to the homepage of the binder):
=NamedRange("Cl_Name","Client Name")
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.
Scope
The scope should always be set to the name of the worksheet, in this case 'N25 Fines Penalties'.
WARNING: Avoid the use of 'Workbook' level named ranges.
The Active Workpapers platform enables you to import a template more than once, so 'Workbook' level named ranges frequently give rise to naming conflicts.
Finalise
Once happy with the information, click OK.
