Using the Microsoft Excel Add-In

PRINT THIS DOCUMENT - USING THE MICROSOFT EXCEL ADD-IN (PDF)

Click here to go Back to the Main Help Page.

Quick Links
Sample Excel Files

The UAAR DataLog allows Microsoft Excel to pull data directly from your sales data into worksheets. This gives appraisers the flexibility to create custom reports based on sale data. Worksheets can be created that automatically perform complex calculations and analysis as defined by the appraiser.

The UAAR DataLog Excel Integration works with Excel 2002 (part of Office XP) or newer versions.

Install the Excel Add-In

In order to access the DataLog from Excel, the Add-In must be installed. Run Excel and click on the menu "Tools -> Add-Ins".

On the screen that appears click on the "Automation..." button.

Next find the item "UAAR DataLog Excel Integration" in the list of available automation servers. This list will be very long. The easiest way to find this item is to click in the list box and press the letter "u" on your keyboard. This will cause the list to jump down to the first item starting with "u". This should be very close to the "UAAR DataLog Excel Integration" item.

Click on OK for any screens that are now open until you are back to the main Excel window.

Importing Data

Now that the Add-In is installed, Excel can use a new function called "DataLogLookup". The function takes two parameters. The first is a string that represents the number of the sale to use. This parameter should be formatted the same way "d" or "i" numbers are formatted in ClickFORMS. The second parameter is the name of the field that should be imported.

This function works the same way as any other Excel function. Simply type "=DataLogLookup("d1", "grantor")" in any field and the value of the Grantor field for your first sale will appear in the cell.

The parameters for the formula can be cell references just like any other Excel formula. This provides a powerful tool to set up custom reusable templates. For example, consider this formula: "=DataLogLookup(A2, "grantor")". Notice that A2 is not in quotes as the d1 was in the previous example. With this formula you can now type a "d" or "i" number into cell A2 and have the corresponding sale data imported into the formula's cell. If you need to use a different sale, you can simply type a different number into cell A2 (including the "d" or "i" prefix, just like in ClickFORMS). In this manner you can set up custom templates that import sales data with the same simple steps used by ClickFORMS.

Most of the time more than one field will be imported from a sale. When Excel copies formulas, it tries to adjust any cell references to reflect the new location. This can make it difficult to copy the DataLogLookup function from one cell to another. All of the copied functions should reference the same field for the database number parameter. An easy way to avoid this problem is to name the cell that contains the database number. Named cells will not be adjusted as formulas are copied from one cell to the next. You can set the name of a cell by typing in the "Name box" located on the left side of the window. In this example we have changed the name of cell C4 to "Sale2". Formulas can now reference this sale using the cell name as in this example: "=DataLogLookup(Sale2, "grantor")"

As a starting place for developing your own Excel templates, AgWare is providing three sample templates. These templates contain examples on how to import your data and perform custom calculations.

All Fields Example.xls: This file simply imports every possible field from the sale. The name of each field is listed in the left hand column. This file provides an easy reference to find the field names used in the formulas.
One Page Sales Sheet.xls: This file duplicates the UAAR One Page Sales sheet. It can be used as an example of how to create your own sale sheet format to use in word processing programs.
Land Mix.xls: This file duplicates the UAAR Land Mix Grid. It shows how to perform calculations based on the imported sales data.

Error Codes

When Excel updates a cell containing the DataLogLookup formula, it will search for that sale in the DataLog. If the sale and field is found, the corresponding field value will be put into the cell. There are three other possible values that can be put into the cell.

If the sale cannot be located based on the database id or index, the value of the cell will be set to "#NoSale#".
If a sale is found but the field name is not recognized by the program, the value "#UnknownField#" will be placed in the cell.
If the sale number parameter is blank, then the value returned by the formula will be "0" (zero).
If the sale and field are both found but the field is empty, the value returned will be "0" (zero)

Saving Your Results

Excel keeps track of when specific cells need to be refreshed and will call the correct functions. This can cause problems if you want to keep a permanent copy of your exported data. If you were to make changes to the worksheet at a later date, the sale may no longer exist in your database. When the function is called the resulting cells will be filled with "#NoSale#". If the sale number does exist, then the current data will be overwritten with the new data from the DataLog.

To prevent your data from being recalculated you can simply copy the data from your master spreadsheet to another spreadsheet. When pasting your data into a new sheet use the menu command "Edit -> Paste Special". This allows you to change the paste setting to "Values and number formats". This setting will strip out the Excel formulas and paste the actual formula results.

You can now change the copied spreadsheet without the DataLog being contacted to provide new data. If you need to import different sales, you can continue to use the original spreadsheet.