Microsoft Excel

4 minute read

Excel Challenge 34: Advanced Data Validation

Deb Ashby

Deb Ashby

Join the Excel conversation on Slack

Ask a question or join the conversation for all things Excel on our Slack channel.

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!

Take the challenge

Download your free practice file here.

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. 

excel-challenge-search-part

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. 

excel-challenge-parts-catalogue

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

excel-challenge-vlookup

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. 

excel-challenge-part-not-found

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”. 

excel-challenge-data-validation

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”. 

excel-challenge-error-message

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:

GoSkills Excel challenge     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!

Take the challenge

Download your free practice file here.


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.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial

Join the Excel conversation on Slack

Ask a question or join the conversation for all things Excel on our Slack channel.

Deb Ashby

Deb Ashby

Deborah is a Microsoft MVP and TAP Accredited Microsoft Instructor and Content Creator. She has had various careers in the IT industry since the mid 1990s. This includes various roles in IT training, including the starting of her own company where she focuses on creating video-led, Microsoft training courses for clients. She is an instructor at GoSkills with various Microsoft courses, including Excel for Business Analysts. In her spare time, she likes to swim, practice yoga, travel, and refine her photography skills. View her profile here.