Transmittal MS Excel® Template

Setup required before use	Who can use it?	Where is it?
Transmittals Project Document Library Drawing Register Project Contacts Staff records	Create template allowed by System Administrator   All user access levels can use the template.	Project > Documents> Transmittal Summary > Print

The MS Excel® templates are stored within the Synergy folder on the server. This is the location selected within the Office settings for the document library, and is where all the Synergy data for the office is stored. Within the Synergy folder locate the following file:

Synergy folder\Excel Templates\Transmittals\Template.xlsx

Double click the template file to begin the customisation process.

Personalise the MS Excel® template to include the contact details for your company or office location.

1. Open the Template.xlsx file.
2. Use the Cells in Row 1 to enter the company details. The row 1 cells from D-AA, or row 2 from cell P - AA can be used to enter customised text to be shown on the excel output.
3. Insert a logo image into the same cells that can be used for text entry above.
4. Or insert additional rows above the existing Row A at the top of the Excel document, and use the additional rows to enter the company details and store the company logo.

Format the MS Excel® template file to place the Synergy bookmarks in the custom location required for your template. Some bookmarks are grouped together in a 'table' and must be kept together in the template. Other bookmarks are individual and can be moved to any location in the MS Excel® file.

The tables in the document are:

1. Dates Table - The approved date for the last 24 transmittals.
2. Transmittal Table - Transmittal Number, sending method, and reason for sending.
3. Drawings Table - Drawing Number, Title, and Version number.
4. Contacts Table - Name, Email, and Included indicator.
5. Staff Table - Name, Email, and Included indicator.

Examples of other individual bookmarks are Project Number, and Project Title. Copy and paste these bookmarks to any location in the MS Excel® template.

Update the location of the Synergy bookmarks in the document by:

1. Move a table by:
   1. Cut the entire table row
   2. Right click on the row number where you want to paste the table, and select 'Insert cut cells' from the menu.
   3. The row is in a new location in the MS Excel® file.
   4. Repeat this for each table of bookmarks that requires a new location.
2. Move a Synergy bookmark by:
   1. Select the cell that contains the bookmark.
   2. Press CTRL X to cut the entire bookmark.
   3. Select the cell the bookmark will be moved to in the MS Excel® sheet.
   4. Press CTRL V to paste the bookmark.
   5. Repeat this for each bookmark that needs a new location.

Change the text that is shown as the bookmark label if you prefer a different label to the defaults shown. Please make sure that the label and the bookmark are always in different cells. i.e. The label 'Methods' cannot be in the same cell as the bookmark [METHODS_CSV]. If you place the label in Column A, and the bookmark in Column B, then the MS Excel® output will populate correctly.

Update the labels by:

1. Over type the text shown on the existing label field.
2. Copy and paste the label to an alternate location.

Next change the font to be your corporate company font type, adjust the font size, colour, and alignment. The default font applied to the template is Verdana. This font has been applied as it is available in all versions of MS Excel®. Suggested formatting to be done in the MS Excel® file is:

1. Change the text 'Transmittal Summary' to be your own custom document heading.
2. Update the colours of the document heading and Project Number and Title bookmarks to your corporate company colour.
3. Change the text on the table grouping rows 'Drawings' or 'Distribution List' if required.
4. Update the colours on the table grouping rows 'Drawings' and 'Distribution.
5. Update the alignment of the Synergy bookmarks to be all Left Aligned or Centred.
6. Select a bookmark and change the font or size. This can be changed on any table or individual bookmark.

Save the template after adjusting the it to match your companies requirements. The template must always be saved to the Synergy folder\Excel Templates\Transmittals folder using the file name 'Template.xlsx'.

Sample	Template	Sample Output
1	The default MS Excel® template.	Default template output.
2	Adjust the location of the bookmarks to have the distribution table at the top of the excel sheet. Include the company logo and contact details in the header, and adjust the default template font and colours.	Sample of the adjusted output.
3	Move the transmittal date table to be shown above the drawings table. Include the company logo and contact details in the header, and adjust the default template font and colours.	Sample of the adjusted output.

1. General Bookmarks - Summary details of the Project and Transmittal.
   1. Project NumberThe project number is displayed.
   2. Project TitleThe Project Title from the Project > General tab.
   3. Project ReferenceThe Project Reference from the Project > General tab.
   4. Project ManagerThe Project Manager selected on the Project > General tab.
   5. Project AddressAdd the project address to the template, displaying either the full project address formatting using the system settings, or use the individual address bookmarks: project address line 1 & 2, town, state, post code.
   6. Methods CSVDisplays the key for the Methods field from the Transmittal General Tab. For each Method code displayed the description is shown. If the method is not shown in the MS Excel® output, then the code and description are not shown in the key.
   7. Reasons CSVDisplays the key for the Reasons field from the Transmittal General Tab. For each Reason code displayed the description is shown. If the reason is not shown in the MS Excel® output, then the code and description are not shown in the key.
   8. Transmittal Sent DateThe sent date of the most recently added transmittal.
   9. Transmittal Primary Contact EmailThe email address of the primary contact for the most recently added transmittal.
   10. Transmittal NoteThe note entered on Transmittal > General tab for the most recently added transmittal.
   11. Transmittal Primary ContactThe Primary Contact Company or Individual Name for the most recently added transmittal. This bookmark is used if you need to address the transmittal export to a contact.
   12. Transmittal Primary Contact Personnel NameThe Primary Contact Personnel Name for the most recently added transmittal. This bookmark is used if you need to make the transmittal show as 'Attention To' a person's name.
   13. Transmittal Primary Contact AddressAdd the primary contact address to the template from the primary contact of the last transmittal. The address is displayed either using the full project address formatting using the system settings, or using the individual address bookmarks: project address line 1 & 2, town, state, post code.
2. Dates Table - The approved date for the last 24 transmittals is shown. The transmittals are sorted by date order in the export, from oldest shown on the left to the newest transmittal date on the right.
   1. Transmittal YY - MM - DD*The Year, Month and Date are displayed in the Dates table. This will show the approved date for each Transmittal. The year is shown in a 2 digit format, i.e. the year 2013 is shown as 13.
3. Transmittal Table - History of the general details for the shown transmittals for this project.
   1. Transmittal Project Number*This number reflects how many transmittals exist within this project.
   2. Transmittal System Number*The unique number in Synergy to reflect how many transmittals exist in the entire database.
   3. Transmittal Method*The Method Code setup within Default Data > Transmittals. The description is displayed in the Methods CSV bookmark below and can be used as a key.
   4. Transmittal Reason*The Reason Code setup within Default Data > Transmittals. The description is displayed in the Reasons CSV bookmark below and can be used as a key.
   5. # of copies*The number of copies entered for each Transmittal is displayed. Entered on the Transmittal General Tab.
4. Drawings Table - History of which drawings are sent on each transmittal.
   1. Drawing NumberThe prefix and number assigned in the drawing register.
   2. Drawing TitleThe drawing title entered in the drawing register.
   3. Sub ProjectThe drawing sub project selected in the drawing register.
   4. Document TypeThe drawing document type selected in the drawing register.
   5. File TypeThe drawing file type selected in the drawing register.
   6. SizeThe drawing size selected in the drawing register.
   7. ScaleThe drawing scale selected in the drawing register.
   8. NoteThe note entered on the transmittal drawings tab for the drawing row. This is often used to enter an exception about how one drawing file will be sent out. i.e. This drawing will be printed, 2 copies sent.
   9. Current VersionThe most recent version # assigned to the drawing register row is shown. Used to compare the version number that was sent, against the most recent version number available.
   10. Transmittal Drawing Version*The version number that has been included on each of the displayed transmittals.
   11. Client drawing numberThe external reference number for the drawing provided by the client. This is an optional field in the drawing register.
   12. Drawing progressThe percentage complete for the document entered for the drawing in the register.
   13. Drawing link dateThe date that the project document was last updated in the drawing register i.e. Updated link to document or transmittal version created.
5. Contacts Table - History of which contacts each transmittal has been sent to.
   1. Contact NameThe company contact name, or the individual contact first and last name is shown.
   2. Contact and PersonnelThe company contact name and personnel name, or the individual contact name are shown.
   3. PersonnelThe personnel name selected for the Project Contact is shown.
   4. CategoryThe category of the contact selected on the Project Contacts tab.
   5. Contact EmailThe email address for the company personnel selected on the Project > Contacts tab.
   6. Transmittal Contact*If the project contact is included on the transmittal then 'Y' is shown. When not included the field is blank.
6. Staff Table - History of which internal staff each transmittal has been sent to.
   1. Staff NameThe first and last name setup in Staff and Application Security List is shown.
   2. Staff EmailThe email address for the internal staff member is shown.
   3. Transmittal Staff*If the staff member is included on the transmittal then 'Y' is shown. When not included the field is blank.