Excel information reference and Mapping preview.

Within most companies, locations (e.g. shop, or office) are usually referred to by a code rather than a full name, or address. This significantly cuts down on the space requirements necessary to hold the data, and makes entering the code itself quick and easy. It can also be an inconvenience when the code itself, or the name of the location isn't known.

Often a support query will reference details on a report, stating that the details appear incorrect or querying why the details are as they are. The first step in answering this query will usually be to check that query against the report itself. But if the user has only given the location name, and not the location code, this would need to be looked up first.

So we find ourselves continuously (and laboriously) referring to some sort of reference guide to look up the details. Until now …

The Branch Information and Mapping tool started life to avoid this issue, linking the store numbers held in the selected cells to a query against the branch information database, and showing the results on a form – as shown in the example below ;

So in the above example, I entered four branch numbers, and clicked on the icon shaped like a letterbox and letter. This launched the branch_info add-in, selecting the fields shown from the branch info database for the selected stores.

Clicking on the Next Store Button will show the details for store 2008, and once there, clicking previous will go back to store 2007 and so on.

Clicking on the “List View” Radio button will switch from showing one store at a time, to all on one window …

Note, we have scroll bars here to slide the view across to the other columns on the extracted data, and we can click on the “Form View” radio button to return to the previous form view.

The extracted data includes the postcode on the address for each individual store. This information can be input against many individual web sites to show the selected store on a map. So, with Form View being displayed, against store 2007, clicking “MultiMap” will open bing maps using the postcode store Aberdeen ;

Clicking the GoogleMaps button would open GoogleMaps with the postcode, while TescoMap would open the Tesco store locator web site with that postcode. The Acorn Profile no longer works as the hosting web site has been replaced with a take-over site that no longer includes this useful information, but setting up any of these buttons (or adding new ones) to display new web-links is easy.

Clicking on the Export button would open a sub-form allowing the data to be exported onto the current sheet, or a new sheet – this option can also include more options to display the selected locations together on one map, but more information on this later.

See below for an example of clicking the export button exported in seconds ;

As per the details above, I had clicked on the letterbox icon shown above, and this had returned specific columns set up for that icon. Other buttons to the right of this allow different data selections to be extracted, as shown below ;

So here I’ve clicked on the binoculas icon, and different data is shown, with more location information displayed. You’ll notice that the “View on Map” button is now available. This is because the extract also included the “Grid_Ref_Northing” and “Grid_Ref_Easting” positions of the store. This enables us to show all stores on a map just by clicking on this button ;

The final point on the map was not included, as the grid X / Y information wasn’t available for this store. This helps us to gain more suitable mapping information than a single point on a map, but the export option allows more GIS related information – read on for more details...

Plus, it's not just about geography. Mapping information can be visually enlightening when looking at data within other locations. Check out the example below where data has been mapped out within a supermarket, in this case showing "widgets" within different areas of the supermarket represented by different point sizes / colours within the exact area of the supermarket that they exist. The data plotted could just as easily be sales / stock / customer throughput / etc... Read More ...

As mentioned above, the lookup of data is not simply limited to the location code. If I knew the name of the location, or part of the name, I could look up the location code and other related information by entering that name ...

There are many other options for searching by other fields, with the SQL Wizrad icon available for creating complex location search queries, and the magic wand available for setting up new icons on the toolbar to return different search criteria. The location lookup tool can be configured to work against any input source, so it is not just locations that can be looked up, but products for example. Read on for more details of these features ...