Look Who it Was Random video

Excel - Data Validation Fix: Solving Spelling Errors & Trailing Spaces in Drop-Down - Episode 1162

297 views · Published 21 December 2009 · 2:45 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Data Validation Fix: Solving Spelling Errors & Trailing Spaces in Drop-Down.

Welcome back to the MrExcel netcast! In today's episode, we're tackling a question that was sent in by one of our viewers, Shaun. He's trying to create a drop-down menu that will automatically bring over a section of a report when a certain option is selected. Sounds simple enough, right? Well, I thought so too until I ran into some unexpected issues.

As I was trying to figure out the solution, I realized that the drop-down menu was not working properly. When I used the =MATCH function to find the value of "January", I kept getting an #N/A error. After some investigation, I discovered that the word "January" was spelled differently in the drop-down menu and the report. It may seem like a small mistake, but it was causing a lot of problems.

Upon further examination, I found out that the drop-down menu was pulling the options from a list called "MONTHS", which was stored on a different sheet. This is where things got interesting. The list had trailing spaces at the end of each word, which was causing the mismatch in spelling. This is a clever setup by Shaun, but unfortunately, it was causing a lot of headaches.

To fix this issue, I went to the Formula Name Manager and found the list on the "YEAR" sheet. After removing the trailing spaces, the drop-down menu started working perfectly. This may seem like a small fix, but it's crucial to pay attention to these details when working with data validation. Otherwise, it can lead to a lot of confusion and errors.

In tomorrow's episode, we'll be using the information we gathered today to complete Shaun's request and bring over the rest of his report. So make sure to tune in for that. And as always, thank you for watching the MrExcel netcast. Don't forget to subscribe and hit the notification bell to stay updated on our latest episodes. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction and Sponsorship
(00:14) Difficulty with Drop-Down Function
(00:26) Using =MATCH to Find Value
(00:49) Troubleshooting with Data Validation
(01:31) Identifying and Fixing Trailing Spaces
(01:51) Impact of Spaces on Functionality
(02:01) Initial Problems and Tomorrow's Solution
(02:29) Clicking Like really helps the algorithm

This video answers these common search terms:
Data Validation
Data validation troubleshooting
Drop-down menu
Formula Name Manager
MATCH function
MrExcel podcast
Pulling data from another sheet
Relative row
Report section
Spelling error
Trailing space


A simple MATCH formula is not working. In episode 1162, we wander through the Validation dialog, the Name Manager dialog, all trying to figure out why the MATCH formula is returning #N/A.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152777/

More from this channel