Data Validation Quirk When Using Named Ranges

So here's a goofy item I stumbled upon today. (Others of you, I realize, have known about this for some time.)

Here's the situation:

1. You are using Data Validation to restrict input for a particular cell to items from a list (Data --} Data Validation --} List).
2. You have the "Ignore Blanks" option checked.
3. The list you are referencing is a Named Range.
4. The list you are referencing contains one or more blank cells.

What happens, you ask? Well, it lets users input any value into the cell that's supposed to be data validated without warning. Nice, right?

Three simple options to fix:

1. Uncheck the "Ignore Blanks" option.
2. Don't have any blanks in your Named Range.
3. Don't use a Named Range.

Just thought I'd pass it along...

Later,

Excel_Geek

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