Creating Excel Connect Fields

Excel Connect is used to populate your firm and client information, as well as Trial Balance, P&L, and Balance Sheet figures into your excel documents.

For example, you can create an Excel worksheet that provides information about your Clients, the File's year-end date, as well as key values from your Trial Balance or Accounting Structure.

Creating your own Templates

To create your own excel templates with data fields, please follow the steps below:

1. Create a new excel worksheet (desktop mode)

2. From the "Formula" tab, open the "Name Manager" feature

3. You will now be able to add the available formula appropriate to your file. Please see the Common Input Names section below to add the relevant formula from MyWorkpapers to your excel document.

4. Once you have all the formulas available in your Excel file, you can start adding them to your worksheets where appropriate, to add the formula into your worksheet cells, simply type the following: =MWP 

Excel Connect Names

In order to be recognised as an Excel Connect workbook, define the name:

MWPTrigger.<tb|bs|pl>.final.<name>

e.g. MWPTrigger.bs.final.Total_Fixed_Assets

this Name just needs to exist, it does not need to be used, and it's 'Refers To' field can just be: =""

Note: you will also need to tick the 'Excel Connect' Checkbox on the Workpaper for Excel Connect to activate.

Common Input Names

To show the status of the workbook, define a cell with the name:

MWPStatus

This name should point to a cell that contains a formula that calculates the state of the workbook

MWP.client.client_name 

MWP.client.client_id 

MWP.file.audit_period_date 

MWP.file.file_workpaper_reference

Other Input Names

As per the common input names above you need to define the names for values that you want to be populated and then reference those names in formulas.

Note: the default 'Refers To' value of these fields should be 0 ...if you use non-numeric values you are likely to get errors in any formulas that reference these names.

Refer to Trial Balance  accounts using:

MWP.tb.py.<account number>

MWP.tb.initial.<account number>

MWP.tb.final.<account number>

e.g. MWP.tb.final.123

lead schedule and other Accounting Structure (Balance Sheet or Profit and Loss) totals can be referenced using:

MWP.bs.<py|initial|final>.<name> 

MWP.pl.<py|initial|final>.<name>

e.g. MWP.bs.initial.Total_Fixed_Assets

Note: you need to replace any spaces or symbols in the names with '_'

Output Names

to get values to display as outputs, define names like:

MWPOut.<name>

e.g. MWPOut.Total_franking_credits

Note: you cannot put spaces or most symbols in the names, so use '_' if needed

These names should 'Refer To' the cell that contains the value you want to output

Example document: