Event 1 Office Connector Web HelpOnline Help

Quick Tour
Contents Index

 

Introduction

This exercise will give you an introduction to Office Connector and its functions.  In this example, you will:
  • List General Ledger accounts
  • Lookup related budget data
  • Calculate the beginning balance
  • Insert totals

Step 1 - Insert the Query

The driving force behind this example will be the list of GL accounts.  We are going to use a query so the spreadsheet will automatically adapt as your account list changes.
  1. Launch Excel.
  2. Select cell B2.
  3. Click the Query Wizard toolbar button.
  4. Select a data folder and click OK.
  5. Enter your operator ID and password and click OK.  
    Note -You will not be prompted for your password unless Timberline security is enabled.
  6. The Select Table screen is displayed.  Type the G key to select the GL Account table and click Next.
  7. The Select Fields screen is displayed.  Select Account, Title, Current Balance and Current Period Activity.
  8. Click Finish to insert the query.

Step 2 - Lookup the Budget

Since budget amounts are not found in the GL Account table, we'll use the wizard to build an Office Connector function that will lookup the budget amount from the GL Budget table.
  1. Select cell F2.
  2. Type Budget for the heading and hit Enter.
  3. Select cell F3.
  4. Click the Lookup Wizard toolbar button.
  5. The Select Table screen is displayed.  Select the GL Budget table and click Next.
  6. The Key Values screen is displayed.  
    • Budget Year - Uncheck Cell Reference and type 2000.
    • Budget Type - Uncheck Cell Reference and type Approved.
    • Account - Leave Cell Reference checked and type B3.
    • Click Next.
  7. The Select Fields screen is displayed. Select Annual Amount and click Finish to insert the TSLookup function.
  8. Copy the formula down.  You can quickly accomplish this using the fill handle.

Step 3 - Calculate the Beginning Balance

Since we're including the current balance and current period activity, we might also want to see the beginning balance.  We can do that by just using an ordinary Excel formula.
  1. Right-click the column header for column D and select Insert from the popup menu.
  2. Select cell D2
  3. Type Beginning Balance for the heading and hit Enter.
  4. Select cell D3
  5. Type =E3-F3
  6. Copy the formula down.

Step 4 - Insert Totals

To complete the solution, we will add grand totals to the bottom of the data.
  1. In the row under the list of accounts, select cells for columns D, E and F.
  2. Click the Excel AutoSum toolbar button
  3. Format columns D, E and F so that Excel displays the numbers correctly.

See Also



Content updated 8/4/2009
Copyright © 2009 Event 1 Software, Inc.  This documentation may not be copied in full or in part without written permission from Event 1 Software, Inc.