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
Note -
This tutorial example is designed to work with the Timberline Construction Sample Data.
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.
Select the Timberline Construction sample data folder and click OK.
If you have implemented security in Sage Timberline Office, then you will be prompted to log in. Enter your operator ID and password and click OK.
The Select Table screen is displayed. Type the G key to select the GL Account table and click Next.
The Select Fields screen is displayed. Select Account, Account Title, Current Balance and Current Period Activity.
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.
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.
Right-click the column header for column D and select Insert from the popup menu.
Select cell D2.
Type Beginning Balance for the heading and hit Enter.
Select cell D3.
Type =E3-F3
Copy the formula down.
Double-click the column divider between columns D and E to autosize the new column.
Step 4 - Insert Totals
To complete the solution, we will add grand totals to the bottom of the data.
Type Ctrl+End to jump to the bottom row of the data.
Select the cell in column D in the row below the last line of the data and type Totals.
In the same row, select the cells in columns E, F and G.
Click the Excel AutoSum toolbar button.
Format columns E, F and G so that Excel displays the numbers correctly.
Select the new Totals row and apply bold formatting.