This project has a lot of pieces and resulted in something fun and challenging for me but also something quite valuable for the client. A key piece in the success was an Excel User form. Here’s a photo of me in actual battle with the Exceltopus that we roused (and conquered) in the process.
CHALLENGES
- Client’s regular reports don’t integrate needed data
- Regular reports are always several days or weeks behind
NEEDS
- One source for important data
- A customized (easy) way to access data
- A customized (easy) way to load and track data
- Unique layer of customer service for his clients
CRITICAL CHALLENGE: Allowing data entry while protecting certain cells
Because the user isn’t familiar with Excel, we faced a challenge in meeting 3 criteria:
- Allow data input
- Place requirements around the data such that everything is complete
- Protect formulae from accidental deletion or corruption
Excel Tables (a feature that debuted with Office 2007) are an excellent way of loading data of an indefinite amount and formulae will auto fill. One drawback is that Tables cannot be partially protected. Meaning: We can’t allow for entry of a sales amount in Column G and then lock down column H to protect a sales-tax calculation.
A table has to be fully protected or not protected at all
| Hold on a second. I did find a solution but it was more of a work-around than a true solution. Going down that path means building one workaround to handle the drawbacks of another workaround, and building another workaround to tie all of the workarounds together. Not good at all. The VBA code becomes a mess … it’s 24-karat foolhardy to head in that direction as there are beasts much more challenging than the Exceltopus. |
SOLUTION
An elegant User Form. Mind you, it’s not the prettiest but it works–as shown in the screencast.
Benefits of the form:
- We can require certain fields to be populated, and ensure complete information is provided
- Simple, clean input
- No scrolling to look for the bottom of the list
- The form is designed to clear itself after an entry has been saved
- The worksheet/table where the information is stored is always protected
RESULT
- The client has what he wants in one document, rather than data being in several separate reports
- The client has complete control over how current the information is
- He’s better able to track how he’s doing, monitor trends, and make decisions, predictions and plans
Have a look at the screencast for more details.
And as always, I invite you: please keep your data clean.
NOTE: the data in this screencast are 100% bogus in order to protect sensitive information.
Trackbacks/Pingbacks