Excel Challenge 34: Advanced Data Validation

For this Excel challenge, you'll need to flex your data validation and worksheet protection skills.

Everything you need to participate in the challenge can be found on this page. To take part:

• First, watch the challenge video and read the instructions below the video.
• Review the previously published video(s) and article(s) on which the challenge is based.
• Download the Excel worksheet you will use to complete the challenge tasks.
• Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our Slack channel to share your insights and questions with like-minded learners.

The challenge🤺

Here’s the scenario to be solved from the download file:

We work at a warehouse that stores and ships parts to hardware stores across the country. 

We have a small form in a worksheet that enables us to enter in the catalogue number of a part and return the part name, the price and if the part is in or out of stock. 

The form refers to the ‘Parts_Catalogue’ table on a separate worksheet. This table lists all the parts and the associated information for each part. 

The form uses VLOOKUP formulas to lookup the catalogue number and return the part name, price, and stock information from the table. 

Part 1: Modify the VLOOKUP formulas. 

If an incorrect catalogue number OR a catalogue that isn’t listed in the table is entered into cell D5, the VLOOKUP formulas will show #N/A errors in the cells. 

It is your job to make these #N/A errors more meaningful by adding error handling to the VLOOKUP formulas so that if an incorrect catalogue number is entered, the user sees the text “Part Not Found” in the cells. 

Part 2: Restrict data entry

Next, we need to ‘lock down’ cell D5 so that ONLY catalogue numbers of exactly 5 characters can be entered into the cell. 

• In this example, treat the catalogue numbers as text strings. 

Add an input message that will show when the user clicks on cell D5. The input message should contain the text “Please enter the 5-digit catalogue number”. 

Add an error message that will show if a user enters a catalogue number in cell D5 that is longer than 5 characters. The error message should contain the text “Maximum catalogue number length is 5 characters”. 

Part 3: Protect the worksheet

Finally, we need to protect the worksheet so that only cell D5 is editable. All other cells, including the cells that contain the formulas, should be locked. 

• By default, all cells in Excel are locked. 
• You do not need to add a password.

Hints😉

If you need a refresher to get started, here is a nice jumping-off point:

	Read - Dropdown Lists in Excel
	Read - 10 Most Common Excel Errors
	Read - Protecting Excel Worksheets

And if you need it, we have even more Excel resources here. Do you have a unique or creative way to solve this problem? Tell us about it in our Slack channel.

Have fun!

The solution🪄

We hope you enjoyed taking part in this challenge!

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

Learn data validation and other practical, real-world Excel skills in our Basic and Advanced Excel course.