Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Excel Add-in

Functions: References and anchoring

Instead of having the Function Wizard insert component or parameter values directly into formulas, you can store these values in workpaper cells. Formulas can look up the values using cell references. The values in these cells are typically numeric reference codes, which represent various components or parameters.
If you don't know the reference codes for your components or parameters, the Function Wizard can insert the reference codes into the cells you specify.
When you set up cell references, you can also use Excel anchors to retain different formula elements when you copy formulas to other cells.

Select components

The Add-in provides 2 ways for setting up cell references to components in a workpaper:
Option 1
 - Select existing component values from workpaper cells. Use this option if your workpaper already has cells containing component values. With this option, you specify the cell address (for example, A1) that contains the component value. Component values can include the Reference Code, the Name, or both.
Option 1: Select existing component values
Component value
Description
Reference Code
A number that identifies ONESOURCE products or features, such as 1, 2, or 3.
Name
The name of the product or feature. Examples include US Income Tax and TA - Tax Adjustments.
Reference Code:Name
The reference ID and name separated by a colon, such as 1:Tax Provision.
For example, whether the cell at the address A15 contains 1, Tax Provision, or 1:Tax Provision, the Function Wizard can recognize it.
Complete the following to select existing component values:
  1. In Excel, go to
    Workpapers
    , and then
    Functions
    .
  2. Select
    Get data
     or
    Set data
    .
  3. For a component that needs a reference, select the first icon. Don't select any values in the drop-down fields.
  4. Enter the cell reference manually, or select the cells in the workpaper, and then select
    OK
    .
  5. If the selected cell or range has a name, you receive an additional message. Select
    Yes
     if you prefer to use cell or range name instead of the cell address.
  6. If you decide not to use a reference, select the keyboard icon.
  7. Do one of the following:
    1. Set up row and column anchors for the component.
    2. Create references for other components.
    3. Select Next and configure parameters.
Option 2
 - Copy component values from the Function Wizard to workpaper cells. Use this option to copy the wizard's reference code to a workpaper cell. For example, if you want the formula to look up a component in the workpaper, but you don't know the component's reference code, use this option to copy the reference code from the wizard into the workpaper.
Complete the following to copy component reference codes into workpapers:
  1. In Excel, go to
    Workpapers
    , and then
    Functions
    .
  2. Select
    Get data
     or
    Set Data
    .
  3. Select a value for the component you want to store in a workpaper cell.
  4. Select the second icon to copy the reference code to a workpaper cell.
  5. Enter the cell reference manually, or select the cells in the workpaper, and then select
    OK
    .
  6. If the selected cell or range has a name, you receive an additional message. Select
    Yes
     if you prefer to use cell or range name instead of the cell address.
  7. If you decide not to use a reference, select the
    keyboard
     icon.
  8. When you finish selecting components, select
    Next
    .
  9. Move to the next section of this article.

Configure parameters

You can create cell references for formula parameters, and the routine's similar to creating cell references to components.
You can configure workpaper parameters in 2 ways, depending on whether your workpaper already has parameters listed in cells:
Option 1
 - Select existing parameter values from workpaper cells. Use this option if your workpaper already has cells containing parameter reference codes. With this option, you specify the cell address (for example, A1) that contains the parameter reference codes.
  1. Go to
    Select Parameters
    , and select the first icon. Don't select any values in the parameter fields.
  2. Enter the cell reference manually, or select the cells in the workpaper, and then select
    OK
    .
  3. After you select the cell, the reference code disappears, and the cell address or name's inserted into the formula.
  4. If the selected cell or range has a name, you receive an additional message. Select
    Yes
     if you prefer to use cell or range name instead of the cell address.
  5. If you decide not to use a reference, select the keyboard icon.
  6. Do one of the following:
    1. Set up row and column anchors.
    2. Created references for other parameters in Selected Parameters.
    3. Select Next and complete the remaining steps in the Function Wizard.
Option 2
 - Copy parameter values from the Function Wizard to workpaper cells. Use this option to create a new parameter value by copying the wizard's reference code to a workpaper cell.
  1. Go to
    Select Parameters
    , select a value for the parameter you want to store in a workpaper cell. This inserts a value in the Reference Codes, which is the actual value of the parameter sent to ONESOURCE products.
  2. Select the 4th icon to copy the reference code to a workpaper cell.
  3. Enter the cell range addresses manually, or select the cells in the workpaper, and then select
    OK
    .
  4. If the selected cell or range has a name, you receive an additional message. Select
    Yes
     if you prefer to use cell or range name instead of the cell address.
  5. If you decide not to use a reference, select the
    keyboard
     icon.
  6. Do one of the following:
    1. Set up row and column anchors.
    2. Create references or select values for other parameters in Selected Parameters.
    3. Select Next and complete the remaining steps in the Function Wizard.

Select component and parameter anchors

If you configured your parameter references using one of the methods previously explained, you can also configure row and column anchors. Anchors are useful if you're copying formulas to other cells and want you copied formulas to refer to the same parameter cell, row, or column.
note
Skip these next steps if you refer to names of cells or cell ranges instead of addresses. The anchoring is set automatically for named cells or cell ranges.
To configure anchors:
  1. To anchor rows and/or columns for only certain parameters, select the appropriate anchoring options next to each parameter. To anchor columns and/or rows for every parameter, select the first
    Anchoring
     options.
  2. When you finish configuring references and anchors for these parameters, select
    Next
    , and complete the remaining steps in the Function Wizard.

This article applies to:

  • Product: ONESOURCE Workpapers
  • Subject: Add-in