Like our confused feelings for Tom Cruise, our love-hate relationship with Excel rages on.
We’ve railed against Excel’s ugly charts, inactive user community, and plans for the Excel 2007 user interface. Now here I am doing an ankle-snapping reverse to share some cool stuff we’ve done recently to create quick, easy-to-use applications for list management. First, we created an interface for a searchable database of people, like an alumni directory or yellow pages. More recently, we put together a tool to keep track of lists of people - like a classroom attendance log or mailing list manager. It’s all possible in Excel.
First a disclaimer: this isn’t a claim that Excel has proven to be an ideal tool. We ran into all sorts of limitations when we asked Excel to act like a database, work across multiple lists, and input new data. To a certain degree, MS Access may have been a better option.
That said, there are situations where Excel’s interface features (e.g. check boxes, drop-down lists), user familiarity, data manipulation, and VBA fit the bill. For instance, you may find yourself...
- Wanting to give users a tool they are familiar with
- Needing a solution that can be thrown together in weeks, not months
- Facing users who don’t always have Internet access
- Independent of a central database
- Wanting to easily report and analyze your data
Here’s a taste of the two applications we put together recently:
Member Directory
* Search using the first letters of last name
* Filter search by various attributes
* Find members within a specified distance
* Map results in Yahoo! Maps
(Here, your selected list shows up in a Yahoo! map)
Attendance Tracker
* Search for individuals by first letters of last name
* Add one person at a time to the master list
* Create filtered lists and select which people you want to add to your master list
* Save, open, delete lists
* Export lists to a separate data file
* Report on attendance participation by event, participant, date, etc.