Excel information reference and Mapping preview.

As mentioned in previous pages, the Excel informtation reference tool is not limited to looking up location codes, or location names, but can look up significantly more information by defining a search term. For example, if I wanted to look up all branches which had the word LONDON in the fourth line of their address I could enter ; Address_Line4='LONDON' in a cell, and the add-in will work out the full search query, as shown below ;

... and clicking on View on Map will of course insert a map showing the locations (although currently, this is limited to 255 locations, so the map will only show this number of points, but in the case of London stores, this would look at little cluttered until we zoom in ...)

Using search terms in the Excel information reference tool allows us to specify search terms using any field in the information database we are linking to. These may also be used in combination by linking together the clauses using terms such as AND , OR, LIKE, etc... The add-in assists in this matter by adding the SQL wizard. The example below shows the sql wizard being used to define a search for locations where the Manager's title is MRS or Mrs.

From here, we click "Paste into cell", and the search text is pasted into the currently selected cell, ready for us to click on whichever icon we want to return the desired columns from our information database. The results of doing this are shown below ;

Here you can see that the telephone icon was clicked, which has been set up to return the location code, name, telephone number and managers name. The other icons, to the right of the postbox icon, will return different columns. The columns returned, and the icons themselves are set up by the user, and having different icons for different selections makes this a very handy quick reference tool, and especially useful for returning custom selections for preset extracts using the export button. Setting up new icons is simple, just by clicking the magic wand button, which opens a form as shown below ;

On this form, we can see all of the fields available from our information database, and we can see from the scroll bars that many more fields are available. Under the field list is a text box, where we can enter a description for our new extract. This will be shown when someone hovers the mouse over the new icon.

In the next frame, we have the ability to scroll up or down to show previous icons which have already been defined, where we can change their details, or delete them.

The next frame also has spin buttons available for us to select the actual icon to add to the toolbar, or we can directly enter the icon number in the button below. If we click on one of the spin buttons, the add in will ask if we would like a full list of icons pasted into a new sheet in our workbook. Because of the many thousand of icons available, this may be a more preferable method for choosing an icon rather than scrolling through the list. Saying yes to the question will result in all of the available icons, and their icon numbers being placed on a new sheet. You may then click cancel to cancel the new icon, open the new sheet, and look up the icon's code number to directly enter into the icon number box. An example of the pasted icons is shown below ;

Thousands more icons will be available by scrolling down. So, let's say for our example that I'd like to define an extract which will tell me a store's open and closed dates, and I'll use icon 125 which looks like a date selector to add to the toolbar. I would fill in my selections as shown below ;

Having filled in the details and clicked ok, the new button is added to the Excel Information Reference add-in's toolbar are shown below ;

... and finally, making a selection and clicking on this icon will show me the relavent details ;