Updated to use the new Excel 2007
Ribbon user interface.
Leverage the full power of MS Excel to manage the data in an Excel table...
with a custom data management form...
or build one "on the fly."
Best of all, no programming required. Do everything with just Excel's native capabilities, primarily formatting and Data Validation conditions.
The add-in will be available once the documentation is complete.
The help file
Excel includes rudimentary database capabilities with a fairly decent data table capability. Unfortunately, while most modern database software is written to support data stored in tables, they, i.e., tables, are extremely human-unfriendly, indeed, human-hostile. After all, as anyone who has had cause to work with tabular data knows, entering reviewing, auditing, or otherwise working with data in a table like the one below is tedious and extremely error prone. Among the many problems, it is difficult to scan across the same row without inadvertently crossing over to some other row and it is difficult to know if some particular information is already in the table or if it has to be entered. And, once information is entered it is difficult to review or verify.
As anyone with even a rudimentary exposure to Computer Science knows a key principle behind the design of a good database system is the separation of data storage from the user interface. However, there are so many people -- too many by any measure -- who confuse the storage of data with the presentation of the data to the user. In many ways this is understandable since their only exposure to databases is through a spreadsheet program like Excel. And, unfortunately, such a program does nothing to educate them about the correct way to manage a database. If anything, it encourages poor database design. As a result, there are any number of business (and personal) users of Excel who create a 'solution' that is exactly what it shouldn't be -- data storage intricately intertwined with the user interface!
Have you -- and if you haven't it is very likely you know someone who has -- created a workbook in which a separate worksheet stored information about something -- say an employee? In that worksheet you might have a daily log of hours worked. At the end of the year, you create a new workbook and start over with the same structure. Or, maybe, you did that with the customers accounts? One workbook per customer with each worksheet representing each month's customer's dealings with your organization. Or maybe, you switched the design and had one workbook per year with each worksheet holding information about one particular client?
Unfortunately, that structure is just downright wrong. Without getting into technical details, suffice it to say that it makes it difficult, if not outright impossible, to, among other things,
(a) maintain data integrity,
(b) find information,
(c) locate and correct errors,
(d) get an overview of your business, or
(e) analyze the data to track trends, or identify potential problems or new opportunities.
The correct data storage requires that all similar data be stored in a single table. So, information about all customers should be in a single table. Similarly, information about all incoming orders should be in a single table. For a very brief introduction see RDBMS in Excel or search Google.
While storing information in related tables is very computer-friendly, it is far from user-friendly. But is it a user interface? Actually, a table is downright ugly as a user interface, isn't it? And, in any case how does one perform any kind of integrity check on the data if users are allowed unfettered access to the raw data itself? Most systems develop a "form" to provide a user-friendly view of the data and to verify that additions or changes to the data adhere to corporate standards.
Unfortunately, there is no native mechanism in Excel to do that. The most that Excel supports is its "Data Form" capability, which incorporates no elegant formatting or validation capability. John Walkenbach also offers an enhanced version of Excel's data form.
Creating a custom solution of course, requires knowledge of VBA programming or hiring someone who will do the needful for you and that is both time consuming and potentially expensive.
Cost aside, it seems rather redundant to use VBA to build forms when we already have a very powerful tool at our disposal -- the Excel worksheet itself!
While Excel supports a "data validation" capability on a cell-by-cell basis, it's single biggest weakness is that anyone can destroy the validation specification by simply pasting the contents of another cell into it. And, no, disabling the ability to copy and paste would be so user-hostile that I would never consider such a possibility.
