Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Excel Add-in

Functions: Get

Functions help you build workpaper formulas, and you can use it for building Get formulas to pull data from a ONESOURCE product.
Workpapers uses 2 types of Get formulas depending on the product you use.
The Function Wizard detects your product and automatically selects the appropriate type of formula:
  • TRGetField
     - Pulls a single field from a ONESOURCE product into a workpaper.
  • TRGetRange
     - Pulls a range of fields from a ONESOURCE product into a workpaper.
Follow these guidelines for formulas:
  • TRGetField formulas
     - The Add-in can't retrieve more than 3,400 TRGetField formulas at a time. If a workbook has more then 3,400 formulas, use the Retrieve Selected Data function.
  • TRGetRange formulas
     - A GetRange formula can't retrieve more then 2,000,000 cells of data within the Add-in. If a formula retrieves more than 2,000,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 calculations because each instance of these functions is executed when Excel recalculates.
  • Nested formulas
     - Formulas shouldn't contain nested functions since there are some limitations in calculations, including precedence chain calculations.
To create a Get formula:
  1. In Excel, go to
    Workpapers
     and select
    Functions
    .
  2. In the Function Wizard, select
    Get data
    , and then select components to identify your data.
  3. Select
    Next
    .
  4. If you see fields under
    Select Parameters
    , select values in each parameter.
    1. Red asterisks indicate required values.
    2. Some ONESOURCE products offer these parameters:
      1. All (Perpetual)
         - This acts like a wild card that selects all parameter values and includes subsequent new values from the Platform.
      2. All (Point in time)
         - This selects all the current parameter values and doesn't include subsequent new values from the Platform.
  5. Select
    Next
    .
  6. Enter the
    Target Range
    , which is the cell or cells that receive the data from the Platform.
    note
    If you want to anchor Target Range, select the appropriate arrow: the down arrow to anchor the column and the right arrow to anchor the row.
  7. If your formula is TRGetRange, you have the additional option
    Transpose
     to switch the layout of the results from rows to columns (or columns to rows).
    note
    The information icon changes to a checkmark when the formula's complete.
  8. Select
    Done
    . The formula cell's updated with the current date and time. The target cell's highlighted in blue for TRGetField formulas only.

This article applies to:

  • Product: ONESOURCE Workpapers
  • Subject: Add-in