Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Excel Add-in

Functions: Set

The Function Wizard detects your product and automatically selects the appropriate type of formula:
  • TRSetField
     - Pushes a single field into a ONESOURCE product in the Platform.
  • TRSetRange
     - Pushes a range of fields into a ONESOURCE product in Platform.
Follow these guidelines for formulas:
  • TRSetField formulas
     - The Add-in can't send more then 3,400 TRSetField formulas at a time. If a workbook has more than 3,400 formulas, use the
    Send Selected Data
     function.
  • TRSetRange formula
    s - A SetRange formula can't send more than 200,000 cells of data from the Add-in. If a formula needs to send more then 200,000 cells, use the Batches feature.
  • Length
     - Formulas can't exceed 8,192 characters.
  • Volatile functions
     - Formulas shouldn't contain volatile functions, such as RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), and INFO(). Volatile functions slow down Add-in calculates because each instance of these functions is executed when Excel recalculates.
  • Nested functions
     - Formulas shouldn't contain nested functions since there are some limitations in calculations, including precedence chain calculations.
The next 3 sections explain how to create a Set formula using the wizard.
Initial formula steps
  1. In Excel, go to
    Workpapers
    , and then
    Functions
    .
  2. Select
    Select data
    , and then select components.
  3. Select
    Next
    .
  4. If you have fields under
    Select Parameters
    , select values in each parameter. Red asterisks indicate required values.
  5. Make note whether the formula starts with TRSetField or TRSetRange, and then select
    Next
    .
  6. Follow the instructions for the formula type:
    1. TRSetField
      1. Complete the
        Target Range
         field. This specifies the cell to push to the Platform. Since this is a TRSetField formula that only pushes data into one field, only specify one cell for Target Range.
      2. Enter the location of the cell where you want the formula in
        Formula Cell
        .
      3. Select an anchor: the down arrow anchors the column and the right arrow anchors the row.
    2. TRSetRange
      1. Complete the
        Target Range
         field. For TRSetRange, you must specify a cell range, not an individual cell.
      2. Specify the cell, cells, or row to display any error messages in
        Results Range
        . To see error messages, you must also select Disable the validation of data types for TRSetRange formulas in
        Add-in
        ,
        Settings
        ,
        Calculation
        .
      3. Select an anchor: the down arrow anchors the column and the right arrow anchors the row.
      4. Enter the location of the cell where you want the formula in
        Formula Cell
        .
      5. Select
        Transpose
         if you want to switch the layout of the results from rows to columns (or columns to rows).
      6. Select
        Use column mapping
         to specify which fields receive your workpaper data.
    3. Select
      Done
       if you didn't select
      Use column mapping
      ; otherwise, select Next.
    4. If your Target Range includes headers, select
      My table has headers
       and then map each of your columns to a field in the product.
  7. Select
    Done
    .

This article applies to:

  • Product: ONESOURCE Workpapers
  • Subject: Add-in