Using MAPILab to Enable Searchable Application Parameter Spreadsheets

Table of Contents: Select a Link to be Taken to That Section

Executive Summary

What is MAPILab?
How Does It Assist in Searching Through Large Excel documents?
Why are Spreadsheets so Important for Sharing Master Data Parameters in Supply Chain Planning Systems?
How is MAPILab used to Improve the searchability of spreadsheets?

Introduction

Spreadsheets are very handy for keeping all of the parameter data of an application. Each category can be maintained in a different tab of a workbook. Keeping parameter data in this way is important because many systems such as SAP encapsulate the parameter data through many screens that make the data difficult to reach and hides. For any implementation I am analyzing, I like to have all the parameter data available to me in an external spreadsheet. Having this parameter data for different clients also allows me to compare and contrast the settings quickly. Each category of parameter data, which is one area of configuration, can be kept in a different workbook tab. For multiple clients, I now have a similar database for how they have set parameters. Google Docs is a shared spreadsheet but is not approved by customers because its parameter data sits with Google. Secondly, Google Docs Spreadsheets lacks a good cross-workbook term search capability. However, Excel, while it is not a shared document format, it at least does have the ability to set its search to default within the active tab of the spreadsheet. However, in both Excel for Mac and Excel for PC, Excel allows the searching within a workbook.

Mac Excel Worksheet Search

While these are both effective, the functionality is a bit basic. It turns out there is a plug-in for at least the PC version of Excel (not for the Mac version, unfortunately). This plug-in offers a lot more clarity into the results, and it pays off for workbooks with a large number of tabs and where the search term is repeated sometimes. A significant benefit to this add-in is that it creates a separate search window that provides search results that can be navigated between changing the cells highlighted in the Excel sheet.

The Importance of Shared Spreadsheets for Parameter Sharing

Having spreadsheets that are just stored locally and not shared makes less and less sense. Microsoft has been the chief proponent of isolating Excel data as a data island (queue hyper-aggressive Microsoft sales pitch regarding how Excel is now groupware and how SharePoint enables Excel to be shared across the enterprise). However, a full analysis of Microsoft’s arguments would take this article into a tangent, but I don’t find Microsoft’s arguments persuasive.) Five years from now, I think it will be strange to think of Excel documents as locally stored. Perhaps Excel can be the front-end. However, it is increasingly apparent that something like Google Docs will be the backend and the repository of data. Companies can host their own Google Docs on their server, but none of the companies I work with do. However, Excel has so many plugins for it, including many that I use, such as MAPILab and PowerPivot, that it is entrenched as a front-end for Google Docs for some time. Also, performing processing on a large number of records still requires a client of some type.

Also, Google is pushing spreadsheets functionality into areas that Microsoft had not thought of. However, I have yet to run into a company using the collaborative functionality in Google Docs for system parameter management, but this is one of the best uses of Google Docs Spreadsheet. Unfortunately, most companies of any size are disabled from efficiently sharing spreadsheet data due to their use of SharePoint.

MAPILab

Crosstab searching can be performed with a macro in Excel, but I was unhappy with several of the macros that I tested from the internet on this. Secondly, macros tend to offer only basic functionality. However, MAPILab makes a plug-in that costs $40, which provides a full and sophisticated capability with searching and searching and replacing across tabs within a workbook. The MAPILab pop-up window contains a list of all the areas where the search term is found so that the user can choose which to be taken to rather than having to cycle through various matches, which is the way the Excel single tab search works, and which is far less efficient than the MAPILab approach.

MAPILab is a plugin that installs as a tab in Excel. To initiate it, select the Find and Replace button to the left.

You can search a directory on your computer for the spreadsheet, search a recently opened workbook, or select all workbooks you currently have open.

Next, you will be asked if you want to perform a search and replace or just a search. I will select search only.

Now I need to add a search rule. The basic one is to insert the text or number you are looking for. However, below this offers the ability to search in many parts of the spreadsheet. Interestingly, I searched using both “Cells formulas” and “Cell values.” I did not find a difference in my searches, but this is because I was searching for the word “Profile,” which I believe must interpret as both a value and a formula. However, in the event of actual recipes, I could see how this would provide different results.

I thought it was interesting to note that you could also use wildcards, so Pro* would also be acceptable. I thought of some circumstances where this could be helpful.

MAPILab then provides a very accurate result. If I select one, I will be directly taken to this field in the workbook’s appropriate tab.

The term “Profile” appeared 52 times in this workbook. This was the cell I selected in the MAPILab interface. However, it gets better. Because as I select different instances of the results in different workbooks, it does all of this seamlessly.

What MAPILAB Means for Parameter Management

What this does is convert the spreadsheet into a mini database. There are many relational database proponents out there, and I have had the spreadsheet versus database discussion sometimes with them. Certainly, for heavy-duty users, SQL databases have advantages, but they are primarily for data that will eventually support an application. For instance, this blog resides in a SQL database. It does an excellent job of maintaining the site, but I do not interoperate with it. Also, for only keeping track of application parameter data, a multi-tabbed spreadsheet, as long as it also has multi-tabbed searching, works great. Secondly, by keeping the data in a spreadsheet, I can share these parameters with others. I need to gain input from a business community that can evaluate whether the parameters I have extracted meet the business requirements. I can share the spreadsheet by uploading the Excel spreadsheet to Google Docs Spreadsheet or using the Excel plug-in OffiSync to push the spreadsheet out to Google Docs. OffiSync allows Excel to access all of a user’s Google Docs and integrates with the entire MS Office Suite.

Go to top

Conclusion

MAPILab has made a fantastic plug-in, and my hat is off to them. The implications of MAPILabs Excel plug-in are quite significant. There is an enormous number of Excel sheets out there that can be converted into or used as flat-file databases with this tool. Secondly, I have only shown part of what MAPILab can do; it can be used with wildcards and can search across spreadsheets, not just within tabs of a spreadsheet. This means that different spreadsheets can focus on specific areas, but MAPILabs’ global search capability can, in essence, unify them from the perspective of searchability. Once MAPILab finds the search string, it will display them quickly and easily, taking the user directly to the cell in the right tab within the right spreadsheet.