Example of Recreating a Business Form in Excel

I just recently completed an enhancement on a business form I'd done for an Excel_Geek $50 Project customer from a few months back. John is in the insurance business, and wanted me to replicate in Excel a manual, paper form they use, so that it would be easier/quicker to fill out, not to mention neater when printed out.

I think what I created illustrates several of the key reasons why using Excel to recreate otherwise manual, paper business forms makes good sense.

• Using worksheet protection, we can keep users from inadvertently "breaking" the form.
• Using conditional formating, we can visually cue users which areas in the form they can or need to edit or input data.
• Using data validation, we can ensure that only the appropriate data in the appropriate formats are input into the form. (I even created a "pick from list" data validation that dynamically changed the list based upon selections made in other parts of the form. Oooooohhh Aaaaaahhh!)
• And finally, perhaps one of the most compelling reasons to use Excel to recreate manual business forms is that one can use formulas to automate otherwise tedious calculcations.

All of this, and not a single line of VBA!

Thank you, John, for joining the ranks of the Excel_Geek repeat customer club, and to all of my Insiders subscribers, enjoy this file.

To those of you who haven't yet subscribed to the Insiders program, did you know that when you do, you will receive copies of all the $50 Projects I do for others for a year? All for $39.95 per year. That's right. If I do, say, 20 projects per year, you'll get all 20 for less than $2 each. Why would I give to you for nearly free something for which I charged someone else $50? I little economic principle called "marginal benefit." You see, I've already done the real work, which was paid for, so anything additional I get for that work (while merely having to deliver the product via email) has a relatively high net marginal benefit. Besides, how long do you really think I can offer services worth over $100 per hour for a $50 flat fee without some other revenue streams? After all, there is no such thing as a free lunch.

Can you believe the value! Fantastic examples of Excel and VBA projects AND brilliant lessons in economics, too?!? HA! ;-) Just sign up as an Insider today (if you haven't already)!

Later,

Excel_Geek

First Completed Contributor Project - We're Truly Global!

Guess what.

Within literally moments of my call for Excel_Geek contributors, I had a taker. And since I had a quick $50 Project ready to go, I fired it off.

The end result? Excel_Geek completed a project for a Canadian by outsourcing it to a Contributor in Singapore. Wow. I'm still amazed a bit myself.

By the way, thanks for the project George. I'm glad we could quickly create that spreadsheet to automatically print out the specifically laid out name tags for your next event. My contributor in Singapore busted out some darned fine code in short order for this. I was truly impressed by the quality of the VBA. This contributor is indeed a fine VBA programmer, doing in 20 or so lines of code what would likely have taken me at least twice that!

Who else wants to be a contributor?

Not only that, who else needs something done in Excel for $50? I've just increased my bandwidth by many times over. Bring 'em on!

New $50 Project - convert Word form into Excel

Thanks to John (from Texas) for his recent submission of a project.

John wants me to convert a business form that he had in Word into a form with automatically calculating fields in Excel. I busted it out pretty quick last night (and did a damn fine job, if I do say so myself, mirroring the layout, style, fonts, etc.) but have a few questions for John regarding the specifics of the calculations, etc., before I can complete it.

Man, it's been so long since Finance class for me...

The thing that is (still) boggling my mind is how to calculate the payment amount on a loan that has annual simple interest compounded monthly, if there are monthly (easy), annual (easy), quarterly (huh?), and semi-annual (huh?) payment plans. Anyone?

Anyway, I'm still trying to get back to do some fine-tuning for John (from Canada) on his golf league sheet, too...and the projects keep coming in. Don't think that I can't handle them, though. I'll get all the sleep I need when I'm dead!

Later,

Excle_Geek