Inserting variable formulas

Alerts and notices
Leave feedback

Internal Employees: Submit feedback

Contact information (optional):

Leave this blank:

Please tell us how we can make this information more helpful.


Characters left:

Change to CS Support service hours

Our Support department is closed on Friday, April 28th for a company event. However, limited Workpapers CS phone support is available on that date from 9 AM - 5 PM ET.

For licensed users of Accounting CS Workpapers

Use the following procedure to create a variable that can be inserted into the selected cell of a custom Excel workpaper.

  1. Open the workpaper with Microsoft Excel.
  2. Select the cell in which to insert a variable.
  3. In Microsoft Excel, choose Insert Formula Formula button in the Workpapers CS add-in ribbon.
  4. In the Variable Type section, choose Amount or Text.
  5. In the Formula grid, select the Source, Name, and Type for a Text variable, or the Source, Name, Type and Operator for an Amount variable.
  6. When you choose Amount as the variable type, you can define the amount type, period, year, and segment that applies to the entire formula.

Notes

  • You can choose Format Variables format variables in Workpapers CS add-in ribbon to format amounts in the workpaper. Show me

    Format Options Example
    Negative sign Leading, Leading in red, None, Parentheses, Parentheses in red, Trailing, or Trailing in red -$100.00, -$100.00, $100.00, ($100.00), ($100.00), $100.00-, $100.00-
    Zero amounts Blank, Dash, or 0 <blank>, - , 0
    Decimal places 0,1,2,3, or 4 $100, $100.2, $100.21, $100.210, $100.2100
    Dollar percent None, Dollar $, or Percent % <blank>, $100.00, 100%

    Note: Format changes are applied to new variables only and not existing ones. (For example, if decimal precision is set to 4 and a new variable is inserted, and subsequently the decimal precision is reset to 2. The original value of 4 is retained for the variable that already exists, and a decimal precision value of 2 is applied to all subsequent variables.)

  • Amount variables display debits as positive amounts and credits as negative amounts regardless of the classification code that is assigned to the account.
  • If you choose to insert a Text variable into the workpaper with Engagement Properties selected as the Source, and a Date (Beginning Date, Ending Date, or Period End Date) selected in the Name column of the Formula grid, you can choose Format Variables in the Workpapers CS add-in ribbon to select a specified date format. Show me

    Format Options
    Date

    December 31, XXXX

    December 31

    December

    Dec 31, XXXX

    Dec 31

    Dec

    12/31/XXXX

    12/31/XX

  • The contents of the drop-down menus in the Formula grid of the Insert Variable dialog are based on the variable type that is selected.
    Variable type Source Name Type
    Amount

    <Account Grouping names>

    Account

    Account Classification

    Custom field

    Leadsheet Schedule

    Net Income calculation

    Tax code

    <Based on Source selection>

    Balance

    Credit

    Debit

    Text

    <Account Grouping names>

    Account

    Tax code

    Custom field

    Date

    Firm properties

    Client properties

    *Engagement properties

    Workpaper properties

    <Based on Source selection>

    Account number

    Description

    Code

  • When you create a Text variable that flows from *Engagement Properties (Source) and Period End Date (Name), you can specify a period and year for the variable in the Amount Definition section of the Insert Variable dialog.
  • The contents of the drop-down menus in the Amount Definition section of the Insert Variable dialog applies to the entire formula.
    Amount type Period Year

    Beginning Balance

    Transactions

    Unadjusted

    Adjusted

    Report

    Tax

    Other

    Adjusting JE

    Reclassifying JE

    Tax Adjustment JE

    Other JE

    Potential JE

    Budget

    Adjusted Budget

    Budget 3

    Budget 4

    Budget 5

    [CP] Current Period

    [CY] Current YTD

    [CT] Current Year Total

    [CZ] Current Year Cumulative

    [C1]-[C12] Period 1-12

    C Current Year

    1P Prior Year

    2P 2 prior years ago

  • To edit a period for multiple cells in an custom Excel workpaper that is open from the Workpapers CS, choose Properties in the Workpapers CS add-in ribbon and make the appropriate changes for the selected cell. Show me.
    Properties dialog

Inserting inline variables

There are some instances where you may want to insert a single date variable or multiple date variables in the middle of a paragraph of text. Use the following examples to insert a date variables within a paragraph.

Note: The CONCATENATE function that was used in Engagement CS is not compatible with Workpapers CS.

example image Example 1: Insert a single inline date variable

If you need one date inserted into a cell with text in a blank Microsoft Excel workpaper, you can copy and paste the following formula into your workpaper and make the necessary modifications, such as changing the period or date format. Be sure to replace Your text here with your own text and include the quotation marks.

"Your text here"&TEXT(WPCSText("EP<Period End Date'Description><Text'Unadjusted'CP'C'>"),"MMMM DD, YYYY")

This variable produces the following text when you print or preview the report.

"Your text here December 31, 20XX" (where XX is the current year.)

example image Example 2: Insert multiple inline date variables

If you need multiple dates inserted into a cell with text in a blank Microsoft Excel, copy and paste the following formula into your workpaper and make the necessary modifications, such as changing the period or date format. Be sure to replace Your text here with your own text and include the quotation marks.

"Your text here"&TEXT(WPCSText("EP<Period End Date'Description><Text'Unadjusted'CY'C'>"),"MMMM DD, YYYY") &"and"&TEXT(WPCSText("EP<Period End Date'Description><Text'Unadjusted'CY'1P'>"),"YYYY")

This variable produces the following text when you print or preview the report.

"Your text here December 31, 20XX and 20YY" (where XX is the current year and YY is the prior year.)

Related topics

Reporting and Report Designer: Procedures

Report Designer, overview

Adding blank Microsoft Excel and Word workpapers

Working with variables

Share This