By the end of this session, you will be able to generate a Reference File Link formula, use cell references in filters and use Vision XL Recalculate.
Discuss in forum

Use Reference File Link to extract detailed SunSystems data into an Excel worksheet. Reference File Link maintains a direct link to your SunSystems files and creates Excel compatible formulas for each entry. Formula values are updated after recalculating. You can copy these formulas to other locations in your worksheet or use them as references.
The Excel format of the cell to which the information is extracted determines the way in which it is displayed in the worksheet.
It is useful for building complex on screen inquiries that link to a base reference cell. When you change the value in the base reference cell, the linked information changes as well. Reference File Link extracts data from a single code; however, it can output multiple data items relating to that single record from one formula. The first output item must be in the cell where the Reference File Link definition form is opened.
In this topic you are going to extract the Asset Name, Depreciation Percent, Base Gross Value, Base Net Value for a single Asset Code. You use a cell reference on the Excel worksheet for the Asset Code. You change the Asset Code and recalculate the information for another asset code.

Prepare an Excel worksheet shown below:

Click here to see the screenshot
From the Vision menu run Reference File Link function.
Choose AR Fixed Assets table in the Filter window highlight AR\Asset Code and click in Filter box and move you mouse and click on Excel cell A3. This is the cell that contains the value that you wish filter the Reference File Link on. The Cell Reference is transferred to the Asset Code Filter filed, and the value of the cell is displayed, i.e. B0001. Use F4 to change the cell reference from absolute to relative and vice versa. The background of the filter field is yellow to indicate that the filter is reading the value in an Excel cell reference or named range.
Click here to see the screenshot
You now have to select the data items to be transferred to the Output window.
The output window should look like this:
Click here to see the screenshot
To define a target cell highlight an output item and click an Excel cell that you would like to be the target cell for this output item.

The output will look like this:

You will now modify the Asset Code in cell A3 that the Reference File Link formula is using for the Asset Code filter. When you recalculate the worksheet you see the extracted values in cells B3 to F3 change to reflect extracted data for the next Asset Code. Now you have to recalculate the worksheet: “Vision XL” -> “Recalculate”->”Worksheet”.
Discuss in forum

Comments

Only registered users can write comments.
Please login or register.