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
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 cell B2.
toolbar button. Query Wizard Select the
Timberline Construction sample data folder and click . If you have implemented security in Sage 300 Construction and Real Estate, then you will be prompted to log in. Enter your operator ID and password and click
screen is displayed. Type the Select Table key to select the GL Account table and click . The
screen is displayed. Select Select Fields Account, Account Title, Current Balance and Current Period Activity. Click
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.
Select cell F2.
Budget for the heading and hit . Select cell F3.
toolbar button. Lookup Wizard The
screen is displayed. Select the GL Budget table and click Select Table . The
screen is displayed.
Budget Year - Uncheck and type Cell Reference 2011.
Budget Type - Uncheck and type Cell Reference Approved.
Account - Leave checked and type Cell Reference $B3. Click
screen is displayed. Select Select Fields Annual Amount and click to insert the function. TSLookup 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.
Right-click the column header for column D and select
from the popup menu. Select cell D2.
Beginning Balance for the heading and hit . Select cell D3.
=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.
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
toolbar button. Format columns E, F and G so that Excel displays the numbers correctly.
Select the new Totals row and apply bold formatting.