• Linkage Dialog
• Linkage Expressions
• Using the Insert Function Dialog [Excel Only]
• Direct Cell Referencing [Excel Only]

Direct Cell Referencing [Excel Only]

View Related Video: User Defined Functions - Direct Cell Referencing

Direct cell referencing is a method of passing the value of one cell as an argument in a linkage function of another cell. By directly referencing an Excel cell number, you can streamline the link creation process and avoid manually building or modifying each link. This method is only available for User Defined Functions (UDF) links in Excel.

Use direct cell referencing when you can identify dependencies between the resulting value of one cell in the function arguments of another cell. By calling the value of a cell, you can avoid "hard-coding" arguments and create flexible linkages across your document.

• Example 1 - Streamlining link creation process
• Example 2 - Introducing a "Control Cell"
• Example 3 - "Control Cell" to specify client file source

Example 1 - Streamlining link creation process

Assume you have several columns of linkages where for each row the value of column A is an argument in the linkage functions of column B and column C. You can streamline the link creation process for column B and column C using direct cell referencing. In the following example, we have account numbers in column A and leverage these values in column B (account description) and column C (current year report balances).

1. Use the Linkage dialog, Insert Function dialog, or manually specify the account numbers in column A.
2. Create linkages for the first row of column B and column C, but pass the cell number of the corresponding row in the linkage function:
   • Cell B5: cw_actdec("BR",A5) instead of cw_actdesc("BR","S20").
   • Cell C5: cw_act("BR",A5) instead of cw_act("BR","S20").
3. Select cell B5 and copy its contents (Ctrl+C). Highlight cells B6-B10 and paste (Ctrl+V).
4. Select cell C5 and copy its contents (Ctrl+C). Highlight cells C6-C10 and paste (Ctrl+V).
5. Each row in column B and column C now displays the descriptions and balances for its corresponding account number in column A. Excel automatically updates the referenced cell number for the corresponding row in column A.

Example 2 - Introducing a "Control Cell"

In the previous example, we used the values in column A to quickly create linkages in column B and column C. In this example, assume on occasion we want to change column C to display unadjusted balances ("BU") rather than report balances ("BR"). Instead of manually modifying the first argument of each function to "BU" every time, we can introduce a "Control Cell" to modify the balance type.

1. Select a cell to act as the "Control Cell". In this example, the Control Cell (B12) defines the balance type. Type BU in this cell.

2. Modify the linkage functions in column C, passing cell B12 as the first argument instead of "BR".

   • Cell C5: cw_act(B12,A5) instead of cw_act("BR",A5).
   • Cell C6: cw_act(B12,A6) instead of cw_act("BR",A6).
3. Each row in column C now depends on the balance type noted in cell B12. You can change this value to any balance type to modify the calculations in column C.

Tip: If you want to copy/paste cells for each column in this example, specify B12 as B$12 to disable automatic incrementing of cell numbers. For example, specify cell C5 as cw_act(B$12,A5) and copy and paste this cell down the column. Each subsequent row retains the reference to B12, but cell references to column A increment based on the row: C6=cw_act(B$12,A6), C7=cw_act(B$12,A7), etc.

Example 3 - "Control Cell" to specify client file source

In the previous example, we introduced a Control Cell to quickly modify the balance type in column C. In this example, we introduce a second Control Cell to modify the client file source by specifying the optional client_file argument in the cw_act() function:

This approach to direct cell referencing is useful with Connector documents outside the client file structure (as these links require the client_file argument regardless).

Tip: If you want to copy/paste cells for each column in this example, specify B12 as B$12 and B13 as B$13 to disable automatic incrementing of cell numbers. For example, specify cell C5 as cw_act(B$12,A5,B$13) and copy and paste this cell down the column. Each subsequent row retains the reference to B12 and B13, but cell references to column A increment based on the row: C6=cw_act(B$12,A6,B$13), C7=cw_act(B$12,A7,B$13), etc.