By the end of this session, you will be able to create a Data Selector that uses a Workbook file, and then create a Reference File Link that extracts information on the code chosen. You will also create a macro.
Discuss in forum
Overview

Use Data Selector to create and display a dynamic lookup on single or multi-column ranges of data or Vision XL Setup and Workbook files. Data Selector consists of Data Selector Maintenance, where lookups are designed and the Data Selector lookups themselves, used to extract information.
A Data Selector lookup is accessed by a button that runs a lookup macro. Data Selectors, i.e. the buttons, can be displayed anywhere on your worksheet and can be edited at a later stage. There is no limit to the number of Data Selectors that can be added to a worksheet.
Data Selectors extract lookup information to cells relative to the position of the Data Selector on the worksheet. For example, if you copy a Data Selector to a new location on your worksheet, the information is extracted to another cell relative to the position of the new Data Selector.
The source of information displayed in Data Selector lookups is selected using either a defined name, a cell range or Setup and Workbook files within the Vision XL functions, Reference File Fill, Data Fill or Extract Transactions. Using a Setup or Workbook file as the data source ensures the data displayed in the lookup is always current as the file is re-extracted each time the Data Selector is used.
You can also use Data Selectors to reference filtered information from associated reports. For example, a lookup containing current debtors can be used to return a debtor code, which can in turn be used in an Extract Transactions report.
Use Macro Builder to create macros, similar to Excel, that automate Vision XL activities you perform frequently. Macro Builder stores a series of steps, i.e. commands and functions, which can be run whenever you need to perform the task. For example, you can click a macro button on your worksheet that enters a value in a formula, recalculates your worksheet and runs a macro relating to the worksheet then opens a Send Data Periods Setup file in which to save the new data.
Any number of steps can be stored in one macro and you can edit the macro in the same way as you edit an Excel macro.

Let's now cerate a data selector. Create a Reference File Fill displayed below:

Uncheck “Output Table Names” and save this report as a Workbook File.
Excel output:

Click here to see the screenshot
Now select all this Excel range of data and delete it. We don’t need it, it’s saved in the Workbook File now. Save the Excel file, this is required in order to save the Workbook File.
You will now create the Data Selector on cell B2. You will then create a Reference File Link formula that uses cell B2 as a cell reference and outputs account details.
Click here to see the screenshot

In your Excel worksheet enter the information in cells A2-A5 and Reference File Link formulas in cells B3-B5. Now every time you press the Data Selector button, choose a code and then recalculate the worksheet the information about your customers will also be recalculated based on the code chosen in the Data Selector: