Creating Your Own Variables (Excel/Word)

Administrator Help Topic

As an Administrator you can create custom variables.  

Overview of Creating a new variable:

  1. Add the new variable to the raw Base Info tab in the DocVariables named range 
  1. Create the user field for the data in any Raw tab  
  1. Link the field to the Variable Data field in the DocVariables Range using a formula or Named Range 
  1. Add the variable to your content  

Detailed Steps for Adding a new Variable (Excel/Word)

For the following steps we are still using the example of adding a variable for “Primary Customer Service Rep”.  You can add a variable to any Excel tab; in the steps below, we are adding it to the Base Info tab.
 
  1. Go to C:/CorsPro/PQuote/Data/  
  2. Make a copy of the baseinfo.xlsx file before you make any changes, perhaps saving this file to another location.  Saving tab files before making changes is a best practice – it keeps a record of previous versions of the tabs and allows you to restore that file should the new tab not work correctly. 
  1. Open the BaseInfo.xlsx file 
  1. Create a field for the “Primary Customer Service Rep” (for example) 
    • Make sure that the Style for the field is set to InputCell so that in a live Quote File, users will be able to enter data into the cell.  Tabs within a live Quote File are protected, so cells in which users need to enter data will need to be unprotected.  We have created cell a shortcut cell style which is unprotected and has a bold, blue font – the cell Style name is “InputCell”.  Using this font format makes it easier for users to identify cells they can change.  The cells that should be protected (so that users cannot change/edit them) are formatted in black font (using the "Normal" Style).  See The Importance of Styles for more details. 
  1. Find the DocVariables Range by going to the Name box (to the left of the formula bar) and selecting “DocVariables” 
    • The DocVariables range will then be highlighted 
    • The new variable must be within the DocVariables range 
  1. Choose a blank cell in the Variable Column (column K in the screenshot below) and type in the name of your new variable - remember to start and end with the ampersand sign:   
    • Example:  &PrimaryCustRep& 
    • Variables cannot have any spaces 
  1. Then in the Variable Data Column (column L in the screenshot below) reference the data you want to use for the variable by either: 
  1. Save the changes you made to the baseinfo.xlsx file but make sure the name of the file remains “BaseInfo.xlsx” 
  1. Open the document or content in which you want to insert the new variable 
    • Be sure that the variable in the document/content is named exactly the same as the variable you created in the Base Info tab  
    • Example:  Excellent customer service is our primary objective.  To ensure you receive the best level of care, you will have an assigned Primary Customer Service Representative.  Your dedicated representative will be &PrimaryCustRep&. 
  1. Push your changes to Production (see Distribute updates to your users for more details) 

Notice that in the example below we are referencing the user field B7 in the formula for L7.

Note:   
  • Variables cannot be placed in Word text boxes.  The variables must be part of the main document content (which includes headers and footers) in order for the variables to be replaced by the variable data.  Also, variable names cannot contain any spaces. 
  • You can add variables to documents/sections within Library Content, Doc Templates, new content, etc. 
  • When applying formatting to variables, you might notice that the formatting is lost once the variables are replaced with the variable data. This is because during the replacement process, the data overwrites the formatting. To overcome this, apply the formatting (e.g., bold or italics) to the spaces before and after the variable, and when the variable is replaced by the variable data the surrounding formatting will still be retained. 
  • Outline-generated outputs (Excel Tool) will automatically have the variables replaced. 
  • Variables in Word responses (Word Tool) will need to be replaced using the Variables >> Find/replace Variables feature.

Return to Main Support Page