Excel Templates
Contents
1. document as being added to Excel by the add on like Save as and update history ACT mail merge fields etc are grouped under Add Ins in Excel 2007 Creating an Excel Template with ACT fields To create an Excel Template launch Excel Templates for ACT then choose File New Template in Excel Templates for ACT main window 3 Excel Templates w Quote Invoice Maker File Options Help New template Edit template Exit el Templates Templates Quote Detailed ABT m Select an action Open the document in Excel Do not open the document Simply create it and Excel will launch and a popup window with the fields names will automatically open see below Mail Merge Fields ACT gt Excel Field t See Access Level Address 1 f Contact field address 2 Address 3 C My Record field Alternate Extension Alternate Phone Birth Dak Field Label Cty ae Company f Product Field Contact Country W Auto Select Next Auto Move 1 Cell Right Auto Move 1 cell Down Insert Close Note the Product field radio button is only available when using Excel Templates with Quote Invoice Maker Excel Templates for ACT User s Manual If the window does not popup or if you closed it you can open it using the Insert ACT Mail Merge Fields menu Microsoft Excel Project Details XBT File Edit View Insert Format Tools Data Wind cae Re Cells ste Rows Columns worksheet Chart Symbo2. down in your field list once you click Insert e The Auto Move 1 Cell Right will automatically make the cell immediately to the right of the active cell active once you click Insert e The Auto Move 1 Cell Down will automatically make the cell immediately under the active cell active once you Click Insert e At the bottom of the ACT gt Excel field list a field named Date and Time allows to insert the current date and time it will not update itself automatically once inserted Use Excel Format Cells menu to remove the date or time as needed When your template is created make sure you save it using the File Save As Excel Templates for ACT menu Excel Templates for ACT templates are saved with the XBT extension Microsoft Excel Invoice Detailed XBT Edit View Insert Format Tools Data Mew Ctrl M Open Chrl 0 Close cave Ctrl 5 Save 4s Excel Template For 4CT i Save S Save as Web Page Excel Templates for ACT User s Manual 7 Creating a bi directional template The basic same principles apply for creating a bi directional template By combining both ACT gt Excel and Excel ACT fields in one template you can export contact information to the spreadsheet which can then compute the data and send computed data back to the same or other ACT fields Adding an Excel ACT field To create a template follow the same procedure as described above To insert Excel gt ACT fields make sure you sel
3. Another efficient and affordable ACT Add On by eXponenciel Excel Templates for ACT 2008 and up v 6 0 http www exponenciel com Excel Templates for ACT User s Manual 2 Table of content P rpose of the add OM io sddasssicsescasssedecsshoueseauseedssGescaenasrevacesdceaeasaasswedaccosceaeeesrvssescasdaeuscksssacscascaseseies 3 Relationship between Excel Templates and Quote Invoice Maket sssssssssccssssssssssees 3 PS CAT AMION procedur C xa srsdsessedetersscadaccstecseareecdedseucesssaasyeiccacusecdeaveversddeusedsagsauececensesseexavoceadeusetseexes 3 Upgrade from previous Versions sivicdessscssssesseseccecsenscsessvecdcotceseceosavesccadeessdesssvecdentgasedecssonsenedseseeres 3 N te for users of Excel 2007 and Up sscscascceisesadscccescacsdecacsceacechisuceesesccnesceedsseessscdaveseacexensesacssssesdsees 4 Creating an Excel Template with ACT fields ccccccsssssssssssscccccccssssssssccccscccssssssssccsssseeees 4 sermi a Held mm ACU eiaa a a neha dncsbelscatsed sh 5 Wbpati the cellis TOU CIM EY 1 ixccankcn sn duaasla scat stebodageacoucechsisausaaaadasactncteaunccensetehaeaebuadenaseocsuaaenadcees 6 Creating a bi directional template sisisssscsccdsssessssccceseacssadesecezcccsescansessceescedecesenstacevscesuseaasebacesscssuases 7 Addins anexcel AC TIe e a io newaarnronaseen 7 Removine an Excel S ACT field roire aiaa A E A E ea eae 8 Creatine a MEF SER document se ccssvdsisasdelcboedsevdssvesasdvees
4. a menu and choosing Transfer Mapped Data to ACT For automating this process see below Window Help Ena Filter Form Subtotals Validation Table Text bo Columns Consolidate Group and Outline li PivotTable and PivobChart Report Import External Data Ey Transfer Mapped Data to ACT Attachment Options Starting with version 5 1 2 you have the option to attach to the History tab or to the Documents tab See the bottom of the Options gt Preferences window When attaching to the current contact attach to the History tab Documents Tab Automation Options When creating a merged document ACT Excel only you have two automation options to choose from in Excel Templates for ACT main window e if you check Do not open the document Simply Attach it to the current contact the new document will be created in the background and automatically attached to the current contact e If you check Do not open the document Simply Update the current contact if the template is bi directional the new document will be created in the background and if required data will be transferred back to the current contact Excel Templates for ACT User s Manual Il You may check both options so that your ACT fields are automatically updated and the document is attached to the contact Naming the merged document Excel Templates for ACT also helps you automatically name your documents which is particula
5. c 3 returns nc e Miid string x y returns y characters starting at position x Ex Mid Company Inc 3 2 returns mp Excel Templates for ACT User s Manual 20 Examples Here are a few examples UCase Left TBL_CONTACT COMPANYNAME 3 TemplateName extracts the first 3 letters of the company name converts them to uppercase and adds the template name preceded by an hyphen If the Company name is Company Inc and the template name Invoice il will return COM Invoice TemplateName TBL_CONTACT COMPANYNAME Year Month Day will return the template name followed by the company name and the date in this format Invoice Company Inc 20050617 QUO Year Month AutoNumber If today is May 31 2005 and the AutoNumber is 025 it will return QUO 200505025 QUO Year Month AutoNumber If today is May 31 2005 and the AutoNumber is 025 it will return QUO 200505025 TemplateName AutoNumber Initials TBL_CONTACT MANAGERUSERNAME If the contact manager is Chris Huffman and the AutoNumber 025 it will return Invoice025 CH
6. cument will be used to provide values for the naming convention As the field data used to create the name for the Excel file may contain invalid characters for file naming you now have the option to automatically replace these characters with a character of your choice Excel Templates for ACT User s Manual 13 Quote Invoice for ACT Additional Features As we said earlier Quote Invoice for ACT gives you the ability to merge sales opportunities fields in your documents Creating a template with Sales Opportunities fields Creating a template works exactly the same way as described in the preceding chapter You just have one more option in the Mail Merge Fields window Product field Mail Merge Fields ACT gt Excel Field type AdjustedPrice lost f Contact Field CreateDate Discount O My Record Field EditDate ItemMumber U Field Label f Product field Subtotal ActualCloseDate li Suto Select Next Auto Move 1 Cell Right Auta Move 1 cell Gown Insert Close Note that Excel Templates with Quote Invoice Maker allows to merge Product and Opportunity fields In the Mail Merge Fields window see above the Product fields are listed first AdjustedPrice to SubTotal then the Opportunity fields starting at ActualCloseDate The principle is the same you select the field you want to insert and the proper code is inserted in the current cell The difference here is that there may be several A
7. djustedPrice Cost etc fields since you may want to include several products in the same document So the code will be slightly different it is appended with a row index DESCRIPTION UNIT PRICE AMOUNT P ProductName Row P Producthame Row P ProductNarme Row P ProductName Row NOTE The program will automatically assign the next index to your field so you don t have to worry about indexing manually Excel Templates for ACT User s Manual 14 Creating a merged document Once again it works the same way as described in the preceding chapter with one difference When Quote Invoice Maker for ACT detects that there is one or more sales field in the merged document it prompts you to choose which sales opportunities from the current contact you want to include in the document Opportunities List for the Current Contact Dates status All Dates ya Open Won Lost Inactive Status Stage Froduct Coffee Service Qua Initial Commun Bulk Coffee Coffee Service Qua Initial Commun Bulk Coffee 1 Select the sales opportunities you want to include in the document by clicking them If you want to select more than one opportunity hold the CTRL key while clicking 2 If you want to change the order in which they will appear in the merged document use the Up Down arrows to move items up or down 3 Then press OK If the opportunities you select contain m
8. e 45 and Update ACT History Save AS Save as Web Page Excel will then save your document under a name of your choice merged documents are saved with the standard XLS Excel extension and the following window will open Create History Note lf Attach document to the current contact If both items are checked and your preferences are set to attach if Add a Letter sent history record to the History tab the document will be attached to the Letter Sent record Regarding Invoice 2006 01 31 e If you check Attach document to history alone the document will be attached to the current contact either under the History tab or the Documents tab see Attachment Options below e If you check Add a Letter sent history record alone a Letter sent history record will be added with the regarding information that you typed in the appropriate box e If you check both and your preferences are set to attach to the History Tab see Attachment Options below the Letter sent record will be created with the document attached to it If your preferences are set to attach to the Documents tab the Letter sent record will be created without attachment and the document will be attached under the Documents tab Excel Templates for ACT User s Manual 10 Updating ACT fields If your template is bi directional you may manually update ACT with the values of the cells you mapped by going to Excel Dat
9. ect Excel gt ACT in the dropdown box of the Insert Field window Mail Merge Fields Excel gt ACT 7 Field type Access Level Address 1 f Contact Field Address 2 Address 3 e Alternate Extension Alternate Phone Birth Dat Field Label City Company fs Contact Country W Auta Select Next Auto Move 1 Cell Right Auto Move 1 cell Down Insert Close i Insert a descriptive comment in cells with an Excel gt 4CT Field When you insert a field note that it won t actually insert a code in the cell itself but rather it will name the active cell with a coded name ACT_C_fieldName In fact you are not inserting a field but rather mapping a cell to a field in ACT If you check Insert a descriptive comment in cells with an Excel gt ACT field a comment will be inserted in the cell so that when you place your mouse over the cell it will pop up This may be useful to edit your templates Es CEL ACT This cell is mapped to the ACT User 1 Field When your template is created make sure you save it using the File Save As Excel Templates for ACT template menu Excel Templates for ACT User s Manual S NOTE Excel Template for ACT does not import currency signs nor date formats Cell formatting is handled directly by Excel Use the Excel Format Cells command to properly format your data currency signs date format etc Removing an Excel gt ACT field As explained above wh
10. en you insert an Excel ACT field nothing is inserted in the cell but the same is named using a particular syntax ACT_C_fieldName To remove an Excel ACT field you need to delete this name Here is how to proceed click the cell with the Excel ACT field you want to remove Look for the Name Box in the upper left corner of your Excel Window just left of the formula bar File Edit views In this case the cell name is ACT_C_User 1 which means the cell was mapped to the User 1 field _ Names cannot use spaces so spaces are automatically replaced by an interrogation mark Note this name and go to Excel Insert Name Define window In the list of names select the name displayed in the Name Box and click Delete If you used the Insert a descriptive comment in cells with an Excel gt ACT field option do a right click on the cell and select Delete comment to get rid of it Excel Templates for ACT User s Manual 9 Creating a merged document To merge a template with data from the current contact run Excel Templates for ACT then select your template and click OK Excel Templates for ACT will open the template in Excel and replace the field codes with the corresponding field values of the current contact Saving the document To save your merged document use the File Save As and Update ACT History menu Microsoft Excel Invoice Basic1 View Insert Format Tools Data Window Ctrl M Open Chrl 0 Close gave Ctrl 5 Sav
11. l Function Name Comment Picture Diagram abject Hyperlink Ctrl k ACT Mail Merge Fields Inserting a field in a cell The Mail Merge Fields window has a dropdown list on top of the fields list Make sure ACT gt Excel is selected To insert a field in a cell click that cell but don t enter edit mode or you won t be able to continue then select the field in the list and click Insert The field will be inserted in the current cell Mail Merge Fields ACT gt Excel Fanlig Birth Date City Contact C My Record Field Country Create Date Delivery Instructions Department Edit Date f Product Field E m il Extension W Auto Select Next Auto Move 1 Cell Right Field Label Auta Move 1 cell Down Close Excel Templates for ACT User s Manual 6 What if the cell is not empty If the current cell is not empty you will be prompted to choose if you want to overwrite the current cell content or append it Excel Templates for ACT A Do you want to overwrite the current cell Click Yes to overwrite or Mo to append to the cell content w cme Click Yes to overwrite No to append and Cancel to cancel This feature is particularly useful for concatenating fields You can easily create a cell containing City State Zip NOTE To speed up the creation of your templates we added these options e The Auto Select Next option will automatically select the next field
12. l document Excel Templates for ACT also allows to make use of Excel powerful calculation features to update fields in ACT by allowing you to map cells from your Excel spreadsheet to your ACT fields So you could export data to Excel have Excel perform calculations and import the results of these calculations in ACT Relationship between Excel Templates and Quote Invoice Maker Quote Invoice Maker for ACT brings additional features to Excel Templates for ACT Whereas Excel Templates for ACT allows to merge contact fields in your Excel documents Quote Invoice Maker for ACT also allows to merge product and opportunities fields a feature particularly useful to create quotes or invoices directly from your ACT opportunities Installation procedure Download the program file from our download page and double click it to start the installation An icon should have been added to your Contact toolbar to launch Excel Templates directly from within ACT ACT should automatically add an icon to the Contact Detail window and an item to the Tools menu These icon and menu item are the only ways to start your Exponenciel add on Upgrade from previous versions If you are upgrading from a version that supported ACT 5 0 2000 or 6 0 2004 you will have to redo your templates because both versions are not compatible Excel Templates for ACT User s Manual 4 Note for users of Excel 2007 and up All the menus that are shown later in this
13. ore than one product you will have the option to include all products or select the products you want to include Excel Templates The opportunity named Initial Order has more than 1 product Do you want to select individual products If you choose e Cancel the merge will be cancelled e No the merge will include all products of the opportunity e Yes the following window will allow you to select the opportunities and or change the order in which they will be displayed in the merged document by using the up and down arrows Excel Templates for ACT User s Manual 15 E Products ky SubTotal Cheese Gift Basket CGE0G2003 1 00 30 99 _ Once again you may select more than one opportunity by clicking them and maintaining the CTRL key pressed Here is the an example of merged documents DESCRIPTION UNIT PRICE AMOUNT 60 00 0 99 539 40 50 00 35 99 2 079 20 SUBTOTAL f 3 418 60 TAA RATE 5 00 SALES TAX f 170 93 Make all checks payable ta CH Gourmet Gifts TOTAL 4 3 509 53 Excel Templates for ACT User s Manual 16 Automatically numbering your quotes and invoices NOTE Our AutoNumbers for ACT Records add on sold separately allows you to automatically number your opportunities at creation time The number is stored in one of the 8 user fields of the opportunity It works perfectly with Excel Templates This number may be used in lieu of the AutoNumber described below by simply me
14. ou may use functions and system variables see Appendix for more on them To insert fields functions or variables all you need to do is use the drop down lists to select the item you want to insert syntax Editor z iz Pee aal FERFE ma r if Fields e Record Manager Functions Initials K Vanables L l arenes pe 1 TenplateName TEL OFPORTUNITY NAME Initials HANAGER N pN aa al This will insert the proper placeholder in the syntax When the program runs it will automatically replace the placeholders by the real value You may type any character you want inserted anywhere For instance if you want to separate the Template name and the opportunity name by an hyphen type the hyphen in the syntax editor Once your AutoNumber is formatted properly close the window and choose to keep your changes when prompted IMPORTANT NOTES e This feature is particularly important when running in automated mode see above as you won t get an opportunity to manually name the file before it is saved e To get the list of field names go to the Options menu and choose Field Names e Files are automatically incremented as in Document1 XLS Document2 XLS etc so that you never risk to overwrite an existing file e If you include more than one opportunity in your merged document and your naming convention includes opportunity fields the first opportunity of the merged do
15. product To continue using your product passed the trial period please buy a license at www exponenciel com Licenses are issued for one ACT username and will only work for that username You may upgrade at any time from Excel Templates for ACT to Quote Invoice Maker for ACT for a small upgrade fee Support For support contact support exponenciel com Rev Jan 24 2008 v 5 4 0 Excel Templates for ACT User s Manual 19 APPENDIX Variables e AutoNumber returns the AutoNumber e TemplateName returns the template name e Year returns the 4 digit year of today s date Ex 2005 e Month returns the 2 digit month of today s date Ex 05 for May e Monthname returns the name of the current month Ex May e Day returns the 2 digit day of today s date Ex 20 for May 20th e Hour returns the 2 digit hour e Minute returns the 2 digit minutes e Second returns the 2 digit seconds Functions e LCase string converts the string to lowercase Ex LCase Company Inc returns company inc e UCase string converts the string to uppercase Ex UCase Company Inc returns COMPANY INC e Trim string removes leading and trailing spaces Ex Trim Company Inc returns Company Inc Note the semi colon in between the arguments of the functions below e Left string x returns the first x characters of string Ex Left Company Inc 3 returns Com e Right string x returns the last x characters of string Ex Right Company In
16. rating a document you have the option to use the AutoNumber format of your choice and to generate a new number or use another number instead Excel Templates w Quote Invoice Maker Fie Options Help Choose a template OK e Exponenciel Excel Templates Templates Quote Detailed XBT m Do not open the document Simply C Attach it to the current contact Update the current contact f the template is bi directional Auto Number w Generate new number Use this instead 38763 By default the Generate new number option is always checked But in case you are generating a document that was already generated once you would want to select Use this instead and type the number of the original document When selecting this option the Next number stored in the AutoNumber definition is not incremented Hiding errors in cells Quote Invoice Maker scans your document before opening it to figure out 1f it contains Sales fields or not If a cell of your template contains a formula which returns an error message when Excel Templates for ACT User s Manual IS no data is entered in the template Quote Invoice Maker s scan might stop too early So you should hide the error message Here is how to proceed In the cell type ifUSERR your_formula your_formula With this syntax if the formula returns an error the cell will be blank and if not it will contain the result of the formula Registering your
17. rging the opportunity field in which the number is stored in your templates The last field listed in the contact field list that you can insert in a template is called AutoNumber Mail Merge Fields ACT gt Excel Field type ee User 4 User 5 e Contact field User 6 User 7 f My Record field User amp User 9 Web Site Field Label aie Today s Date short C Product field Auto Select Ne Auto Move 1 Cell Right J Auto Move 1 cell Down Insert Close This AutoNumber is useful to automatically generate a quote or invoice number that increments itself every time you generate a new document You may define the format of your AutoNumber s under the Options gt AutoNumbers menu Since you may use different AutoNumbers for your quotes and invoices or other types of documents the AutoNumber module allows to define as many AutoNumbers as you need 3 Autonumbers List Excel Templates for ACT User s Manual 17 You may easily add edit or delete AutoNumbers An AutoNumber is defined by its syntax and the next number to be used The syntax can use any character you want Simply use as a placeholder for the incremented number In the example shown below the next quote number that will be generated will be INV 98763 AutoNumbers Edit Settings Mame Invoice Cancel Syntax INV F Use 5 as a placeholder for your automatically incremented number Hext number to be used 98763 When gene
18. rly useful if you want to include the name of the company of the contact or any information contained in a contact or opportunity field Quote Invoice Maker only NOTE FOR QUOTE INVOICE MAKER USERS You may want to take a look at our AutoNumbers for ACT Records add on which automatically numbers your opportunities at creation time The number is stored in one of the 8 user fields of the opportunity and therefore may be included in the name of your merged document To define the naming convention go to the Options menu and choose Preferences E Preferences Default Locations Defaut Template Falder C Documents and Settings Gilles My Documents VE NETS_AC Defaut Document Location CA Documents and Settings Gilles My Documents Merged documents Naming Convention lemplateName TBL_CONTACT COMPANYNAME Ex Template Name HTBL_CONTACT COMPANYNAME will add the company name to the template name Documents are automatically incremented if another document with the same name exists Leave blank to just skip illegal characters Replace illegal characters with When attaching to the curent contact attach to the History tab Documents Tab Click the Edit button to open the Syntax Editor Syntax Editor x Fields TemplateName Excel Templates for ACT User s Manual 12 The Syntax Editor allows to insert contact fields first drop down and opportunity fields second dropdown Y
19. sadassecnesdewedsassseecnesdesedsadeseusessdeentaavesenesesdouen Sas 9 SAVINS DE dOCUme N nsee e e E EN a a Ta ieia 9 J Garis AC R TTE E A acestensecdaatetactaaunlvudeoeiaceeteeciacetehees 10 Attachment OPHONS eicecsesss sctescsssecscceadeccvenclaccussasstessatiatecctadscsuecclecsstuscedessatiacscsdacs E aaa iaaa eaaa 10 AUTOMATION DUO isnan eaaa aaa a a ea a Eaa 10 Naming the MeEroed doc ment isichisocsd ns sadesiudesnccdeeceaeeiadveudedueduantiadoeastduaswansoelcnabassecenstes sencnaueceses 11 Quote Invoice for ACT Additional Features eeoseoosseooseosssoossecoseosssocsseosseosssessseosseosssossseoso 13 Creating a template with Sales Opportunities fields nesssesoeeenssssssseersssssssseeressssssseee 13 Creant amer Cd COCOMMENT rene seansileeea todas 14 Automatically numbering your quotes and INVOICES sssssssseerssssssseeerrssssssscerrssssssseereress 16 Hidme ertors 1 ee US ereinen a mueboaaashauabeteleadute tease 17 Resistere vonr prod Ci osoei es anana Se EE A eo a a 18 UP PONE ERE E E E O E T E T E E 18 Vana DR Sine A ETR 19 Functions snsnrionmna a iesamecbsedsdatesavsaatasatancddaancuesdadssdasesesuaa vets 19 DROTT E e E EO A E A A E A A E 20 Excel Templates for ACT User s Manual 3 Purpose of the add on Excel Templates for ACT creates a bi directional link between ACT and Excel You may create MS Excel templates the same way you create MS Word templates in ACT ie by inserting fields in an Exce
Download Pdf Manuals
Related Search