Customisations can be made on your own firms smart workpapers.  As all of the worksheets are individual worksheets, to link a cell to a separate worksheet, we use named ranges.


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 can also be seen on the left hand side of the formula bar:


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 add using the name manager above, or simply right-click on the cell and select "Define Name", then set the name.   We suggest setting the name to the format TemplateName_FieldName.  The name is by convention only, the formula's will work with any unique value.


The scope should always be set to the name of the worksheet.  Avoid the use of 'workbook' level named ranges. Because the workpapers system lets you import a workbook 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


In the below example, we will add a formula to automatically insert the fines and penalties amount from the "Fines and Penalties" worksheet into the "Tax Reconciliation - Simplified" worksheet.


To do this,  you will need to open both the worksheet you want to add the formula into, and the worksheet that you are linking to, so for this example both the "Fines and Penalties" worksheet and the "Tax Reconciliation - Simplified" worksheet.


There are two formulas we use to insert a named range, which are specific to the smart workpapers addin:


NamedRange - Use this for formulas linking to the starter file (for example linking the client name on the worksheet to the home page of the starter file)

=NamedRange("Cl_Name","Client Name")



SumNamedRange - Use this for formulas linking to other worksheets (see example below)


For our example, we will be taking the Total Fines and Penalties, with the named range of N25_FinesAndPenalties and inserting it into the Tax Reconciliation.  To do this, go to the cell you wish to insert the formula into.  The forrmula typed into the formula bar up the top will be =SumNamedRange("named range of cell you wish to insert"), which for this example, would be =SumNamedRange("N25_FinesAndPenalties").  



Note the "" around the name of the named range.  These must be present for the formula to work.


The other thing to note is that you are only able to test whether your formula has worked is by inserting both sheets into the starter file.


Other functions using the smart workpaper addin


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.