The Shipping worksheet doesn't contain the word "Standrd", so our VLOOKUP function doesn't return any results.While we could simply type in the correct spelling, that wouldn't prevent someone from making the same mistake in the future.
I'm using Excel 2016 on a Windows 10 64-bit system, but most everything in this article applies to earlier versions (specific instructions for 2003 are limited).For your convenience, you can download the example demonstration .xlsx, and files.Over the next few months, we'll review this built-in feature, from the basics to enhancing with VBA.This month, we're going to start with the basics: how to populate the validation list using static and dynamic lists. Fortunately for Excel users, the Data Validation feature is more than adequate for most requirements.
This process can be lengthy and complex—or it can be simple.
Once you've downloaded our spreadsheet, open the file in Excel or another spreadsheet application.
Cell E7 is using the same VLOOKUP formula we added in Invoice, Part 4: More Shipping Options, but it's no longer pulling in the Shipping Cost correctly.
To make sure this doesn't happen again, we can add data validation to our spreadsheet.
Data validation allows you to control exactly what a user can enter into a cell.
You’ll also encounter (and solve) annoyances when creating forms, importing data, cutting and pasting, navigating in and among worksheets, and more.