User-Defined Fields on Other BFA Worksheets

Did You Know?

As mentioned in a previous newsletter article, the BFA workbook allows you to define up to fifteen calculation columns for use on the Data worksheet. However, you can use some of those same user-defined columns (User Calc Field 1-10; and User Save Amount 1-3) on the Division Totals and Billing Code Totals worksheets in BFA. The user-defined columns can hold formulas and create data not elsewhere on the Data worksheet. The BFA workbook can also convert those formulas to use in the Division Totals and Billing Code Totals worksheets.

Defined Names:

When you enter a formula for such a column, we recommend that you use Defined Names instead of cell references. You can use the F3 key while in the User Calc Field cell to pop up a Defined Names list. [See also Defined Names Tips below.]

Site Settings:

BFA’s Setup worksheet, on the Site Setting level, allows you to indicate how you want the subtotal rows on the Division Totals and Billing Code Totals worksheet to be calculated..

1. In the BFA workbook (preferably in Project Analysis mode), select Site Settings.
   
   
2. On the Setup worksheet, scroll to your user-defined columns (starting around column CX).
3. Go to row 68.
   
4. Select one of the following for each column in use.
   1. F = use the formula for the column (in row 62) converted to the appropriate worksheet (Division Totals or Billing Codes).
   2. N = leave the cell blank. This option is used by Spitfire whenever the resulting value created by a sum or formula is invalid (such as when adding units with different units of measure). The following message will appear in such circumstances.
      
   3. S = use the sum of the constituent amounts. This option is often used for money.
5. Remember to save.

For example, the codes on the following User Calc Field columns would result in the following types of data on the Division Totals worksheet.

During the initial creation of the Division Totals or Billing Code Totals worksheet, the user-defined columns are reviewed and converted for use in the associated worksheet. The conversion remains in place during the session of BFA or until you make changes to the Setup data.

Defined Names Tips

Did you know that a user formula can be configured to

• ONLY operate in either the Cost Code total row OR the Account Category row?
• use one calculation for Cost Code rows and a different formula for Account Category rows?
• target specific attributes, like Account Category, Account Class or Account Type?

Normally, a user-defined column containing a formula is applicable to all row types. However, using Defined Names in the formula increases the flexibility of these fields.

For Example: