You can bring in content from your practice management software and from FYI by including Merge Fields in a Template for Excel spreadsheets. The Merge Fields are set up in the Excel spreadsheet that is used as the Template, for example, to include the "Client Name", "Manager", etc. There is no function to directly add merge fields to external sources in Excel. Therefore, for Spreadsheet Templates in FYI, the recommended way is to include these as Merge Names.
This article contains the following sections:
- Outline of the Steps for creating a Spreadsheet Template with Merge Names
- Step 1 - Download the FYI Merge Names for Excel Spreadsheet Templates.xlsx
- Important - Use the Desktop Version of Excel when adding Merge Names
- Step 2 - Create a Spreadsheet Template in FYI and Enable Merge Names
- Step 3 - Create the Excel Spreadsheet
- Step 4 - For Standard Merge Fields - Copy the Required Merge Names from the FYI Merge Names for Excel Spreadsheet Templates.xlsx
- Step 5 - Test the Spreadsheet Template
- Merge Names for Custom Fields
- Step 1 - Determine the Name of the Merge Field
- Step 2 - Determine if the Custom Field was added in Xero Practice Manager or FYI
- Step 3 - Determine the Name to use when Defining the Merge Name in Excel
- Step 4 - Prepare the Template Spreadsheet ready to add Names for the Custom Fields
- Step 5 - Define the Merge Names in the Spreadsheet
- Step 6 - Add the Name Field to a Cell in the Spreadsheet
- Custom Fields from GreatSoft and from Generic Practice Management Software
Outline of the Steps for creating a Spreadsheet Template with Merge Names
The following is an outline of the steps for creating the Spreadsheet Template with Merge Names. These steps are detailed below:
- Download the FYI Merge Names for Excel Spreadsheet Templates.xlsx from this article. You can use this to copy the Merge Names for the standard FYI Merge Fields into the spreadsheet that is used for the Template.
- In FYI, create a Spreadsheet Template (refer to Creating Templates for Word, Spreadsheet or Presentation).
Important Note: In the drawer of the Excel Template in FYI, enable the option Merge Names. - Either create the Excel spreadsheet that will be used for the Template and drag and drop or upload it to the FYI Template, or edit the spreadsheet from the FYI Template.
Note: You must use the desktop version of Excel as the Excel Name functionality is not available in the Online version of Excel. - In the Excel spreadsheet that will be used for the FYI Template, add the Merge Names for each Merge Field you want to use in that Template.
You can copy any of the standard FYI Merge Fields from the downloaded FYI Merge Names for Excel Spreadsheet Templates.xlsx. You copy any of these into your spreadsheet in the cell in which you want to bring in the content for the Merge Field. Copying the Merge Name adds the Name and its definition. - Any Custom Fields that you want to use in the Template Spreadsheet need to be defined and added directly to the spreadsheet.
The Excel Name is defined so that it matches and references the Merge Field for the Custom Field with the required prefix. The Excel Name that you have defined then needs to be added to the spreadsheet to the cell in which you want to bring in the content for the Merge Field. Refer below to Merge Names for Custom Fields.
Templates can only be set up and maintained by an FYI Admin, or a user in a User Group that has Permissions enabled for Templates (refer to Managing User Groups).
Step 1 - Download the FYI Merge Names for Excel Spreadsheet Templates.xlsx
To add the Merge Names for any of the standard FYI Merge Fields, you can copy these from the FYI Merge Names for Excel Spreadsheet Templates.xlsx.
Click here to download the FYI Merge Names for Excel Spreadsheet Templates.xlsx for a list of Merge Fields that you can copy and paste into your Templates and Stationery (or download the file from the bottom of this article).
Note: If you are using Chrome as your browser, the link may not automatically download the document. You need to right-click on the download link above, select Copy link address and paste it into a new browser tab in the browser's URL field.
Important - Use the Desktop Version of Excel when adding Merge Names
When adding Merge Names to an Excel Spreadsheet, this must be done using the desktop version of Excel. The functionality is not available in the Online version of Excel.
- If you have prepared the Excel spreadsheet and will be using drag and drop or Upload to import it as a Template, you must add the Merge Names to the spreadsheet using the desktop version of Word.
- If you will be editing the Spreadsheet Template from FYI, you need to use the Edit in Desktop function in FYI.
Step 2 - Create a Spreadsheet Template in FYI and Enable Merge Names
In FYI, create the Spreadsheet Template in the usual way (refer to Creating Templates for Word, Spreadsheet or Presentation).
To include Merge Fields as Merge Names in a Template for an Excel Spreadsheet, in the Template in FYI you must enable Merge Names in the Template.
When creating or editing the Spreadsheet Template in FYI, click Merge Names in the Filing section of the template to set this as "On".
Using Merge Names in combination with Merge Fields
It is possible to use a combination of Merge Names and Merge Fields via the Email Template Editor (refer to Including Merge Fields in Excel Templates) by enabling both options in the Filing section of the Template.
This is not recommended.
FYI recommend using Merge Names and to not use Merge Fields via the Email Template Editor. Merge Names are more reliable, especially in large spreadsheets, and they allow you to bring in content to macro enabled spreadsheets.
Using Merge Fields via the Email Template Editor in Excel Templates will be phased out in the near future.
Step 3 - Create the Excel Spreadsheet
You can either create the Excel spreadsheet that will be used for the Template and drag and drop or upload it to the FYI Template, or you can edit the spreadsheet from the FYI Template.
You can set up any text in the spreadsheet, with any formatting, before or after adding or defining the Names.
Important Notes: When creating or editing the spreadsheet outside of FYI, or when creating or editing it from FYI, you must use the desktop version of Excel. The Excel Name functionality is not available in the Online version of Excel.
Always make sure that, from the Formulas menu in Excel, you have Show Formulas disabled.
Step 4 - For Standard Merge Fields - Copy the Required Merge Names from the FYI Merge Names for Excel Spreadsheet Templates.xlsx
- Open the FYI Merge Names for Excel Spreadsheet Templates.xlsx spreadsheet (refer above to Step 1 - Download the FYI Merge Names for Excel Spreadsheet Templates.xlsx).
- In the FYI Merge Names for Excel Spreadsheet Templates.xlsx spreadsheet, find the Merge Name for the standard Merge Field you want.
The Merge Names show in the same way as Word and Email Merge Fields, but with FYI_ at the beginning.
Standard Merge Field in FYI Merge Name in Excel ClientName FYI_ClientName FirstName FYI_FirstName LastName FYI_LastName Phone FYI_Phone
Manager FYI_Manager
CreatedDate FYI_CreatedDate
- Select the field from the left-hand column and copy it.
In the following example, this is copying the Merge Name for the Client Name which is FYI_ClientName.
Note: There is a known issue where the values are not currently populated for the Merge Fields for Manager Phone, Manager Mobile, Partner Phone and Partner Mobile. - Paste the field into the spreadsheet that will be used for the Template in the location that you want to show the contents of the relevant Merge Field.
The formula bar shows the Merge Name with an equals sign.
In the example below, this shows as =FYI_ClientName in the formula bar. - Copy all the standard Merge Names that you want to use into the Template spreadsheet as in the following example.
- Add any formatting required to the Merge Names you have copied into the Template spreadsheet. This formatting will be applied to the content for the Merge Field when a spreadsheet is created from the Template.
Adding the Today's Date
You can add 'today's date' to a spreadsheet using the FYI Merge Field Current Date or using the Excel TODAY function.
- Copy the Merge Name for FYI_CreatedDate from the FYI Merge Names for Excel Spreadsheet Templates.xlsx as above.
The FYI_CreatedDate Merge Name adds the current 'today's date' when a document created from the template and this original creation date is retained whenever the document is opened or downloaded. - Excel TODAY function.
The Excel =TODAY() function adds 'today's date' when the spreadsheet is created from the template, but it will reset the date to the current date whenever the spreadsheet is opened or downloaded.
The following example shows the Merge Name for FYI_CreatedDate added to the spreadsheet for the Template.
Displaying the Name Manager
From the Excel Formulas menu, you can display the Name Manager. This shows that the Merge Names you copied and pasted. These are held in the spreadsheet as Names, not as text.
Step 5 - Test the Spreadsheet Template
If you have created the Excel spreadsheet outside of FYI, drag and drop it or upload it to the FYI Template.
Then test the Spreadsheet Template by creating a Spreadsheet from it. The Spreadsheet that is created can be previewed or edited using the online version of Excel.
When opening a spreadsheet created by the Template using the desktop version of Excel, always make sure that you click Enable Editing and, from the Formulas menu in Excel, make sure you have Show Formulas disabled.
When opening a spreadsheet created by the Template using the desktop version of Excel, you can display the Name Manager and you will see that the Value and Refers to show the content for the Merge Names that have been brought in by the merge.
Merge Names for Custom Fields
For any Custom Fields that you want to use in a Spreadsheet Template, you need to define the Merge Names directly in the spreadsheet.
The following details how to add Merge Names for Custom Fields for a site integrated with Xero Practice Manager or FYI.
For information on Merge Name for Custom Fields for a site integrated with GreatSoft or a site using Generic Practice Management Software using the FYI CSV importer, refer below to Custom Fields from GreatSoft and from Generic Practice Management Software.
Step 1 - Determine the Name of the Merge Field
First, determine the exact name of the Merge Field used for the Custom Fields. The Email Template Editor is useful for this.
The Custom Fields are in the XPM group. The following example shows the Merge Fields for two Custom Fields, "Tax Reporting Period" and "Client Level".
Step 2 - Determine if the Custom Field was added in Xero Practice Manager or FYI
Custom Fields can be added in Xero Practice Manager or directly in FYI.
Determine if the Custom Field was added in your practice management software or added in FYI.
An FYI Admin can display a list of the Custom Fields and this shows the Source as XPM or FYI (refer to Managing Custom Fields).
For this example, the following shows that "Tax Reporting Period" has the Source of "xpm" and "Client Level" has the Source of "fyi".
Step 3 - Determine the Name to use when Defining the Merge Name in Excel
When defining the Merge Name in Excel, the Name must be set correctly.
- Custom Fields added in Xero Practice Manager must have the prefix FYI_xpm_custom_
Note: For Custom Field added in Xero Practice Manager, this needs to have "xpm" in lowercase. - Custom Fields added in FYI must have the prefix FYI_fyi_custom_
When defining the Merge Name, the Name must be in the correct format. It must start with FYI_ (in uppercase).
After the FYI_xpm or the FYI_fyi, the rest of the Name must be exactly the same text and in the same case as the FYI Merge Field.
| Merge Field for the Custom Field (as shown in the Email Editor in FYI) |
Source (as shown in the Custom Fields List in FYI) |
Value for Name |
| XPM_custom_Tax_Reporting_Period | xpm (for a Custom Field added in XPM) |
FYI_xpm_custom_Tax_Reporting_Period |
| XPM_custom_Client_Level | fyi (for a Custom Field added in FYI) |
FYI_fyi_custom_Client_Level |
Step 4 - Prepare the Template Spreadsheet ready to add Names for the Custom Fields
In the spreadsheet that will be used for the Template, you may find it useful to initially add the Name that will be defined for the Custom Fields as part of the text to indicate where the contents of the Custom Field Merge Name will be included.
In the following example, the spreadsheet has been prepared ready to define the Names for the Custom Fields.
Step 5 - Define the Merge Names in the Spreadsheet
For each Custom Field that you want to use in the Template, define a Name in the spreadsheet.
There are various ways to achieve the same result. Refer to your Excel documentation for information on defining Names in a spreadsheet.
The following is a summary of setting up the Names for use in Spreadsheet Templates in FYI. This shows examples of how to set up the Name and Refer to in the Name Definition.
Defining the Merge Names in the Spreadsheet
- In Excel, to define a Name, you do not need to have a specific cell selected.
But if you have added the Merge Fields to the spreadsheet as a guide, position the cursor over the text that will be used to define the Name. - Display the Formulas menu.
- Click Define Name (or click Name Manager and then click New).
The text from the selected cell displays initially as the Name. In this example, FYI_xpm_custom_Tax_Reporting_Period was entered in the spreadsheet as a guide for defining the Name.
Important Note: The Name must be in the correct format and in the correct case. It must start with FYI_ (in uppercase). - Refers to adds text to the Template spreadsheet as a reference to the Name.
This is only shown in the Template spreadsheet. When a spreadsheet is created from the Template, this will be overwritten by the contents of the Custom Field, or blanked out if there is no value for a particular Custom Field.
Refers to initially displays the cell reference of the cell that is currently selected in the spreadsheet.
Refers to must always start with =" and always end with "
There are then various values you can enter for Refers to.
You can enter any text in quotes. For example, ="Tax Reporting Period", or ="The Tax Reporting Period displays here"
You can enter text which shows the Merge Field. For example, ="FYI_xpm_custom_Tax_Reporting_Period"
You can enter a reference to a cell within the spreadsheet to display whatever text is in the referenced cell. For example, =Sheet1!$G$5 will lookup whatever value is currently in worksheet Sheet1 at cell G5.
In the example below, the Refers to has been entered as the same as the Name (but in quotes and starting with =). This follows the same convention as the Merge Names in FYI Merge Names for Excel Spreadsheet Templates.xlsx - Click OK.
- Repeat this to define the Names for all the Custom Fields you want to use in the Template.
You can use the Excel Name Manager function to display the Names that have been defined including the ones for Custom Fields.
Step 6 - Add the Name Field to a Cell in the Spreadsheet
The final step is to add the Name Field to the relevant cell in the spreadsheet. The following is one way to add the Excel Name to a cell.
- Position the cursor in the cell you want the content for the Custom Field to be included.
- Type =FY to display a list of the Names that start with "FY"
- Double-click the Name field that relates to that cell.
The selected Name is added to the formula bar. - Repeat for all the Custom Field Names.
The following is an example of the spreadsheet that is created from the Template created with Merge Names with both standard and Custom Field Merge Fields.
Custom Fields from GreatSoft and from Generic Practice Management Software
For details on the prefix to use for Custom Fields from GreatSoft and from Generic Practice Management Software, please contact the FYI Support Team at support@fyidocs.com.
Download Files