Excel - How to Set Up Check Number Validation & AutoComplete in Excel - Episode 491

1,194 views · Published 21 April 2009 · 5:23 · Indexed 8 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  How to Set Up Check Number Validation & AutoComplete in Excel.

Welcome back to another MrExcel netcast! I'm Bill Jelen, and as you can see, I'm currently on vacation in beautiful Maui. But that won't stop me from bringing you some valuable Excel tips and tricks. Today, we're going to tackle a tricky validation issue that was sent in by Mike. Now, I know this may seem like a complex topic to cover in just one podcast, but let's give it a shot.

Mike's question revolves around setting up a checkbook in Excel. He's using some advanced tools, such as data validation, to create a list of categories and payees. However, he's run into a roadblock when it comes to validating the check number field. Mike wants to ensure that the check number is either an integer or one of the values he has used previously, such as DD for direct deposit or ATM for an ATM withdrawal. He also wants to have a drop-down list for this field. While I can't cover the drop-down list in this podcast, I'll walk you through how to set up the rest of the validation.

In data validation, we often use the custom list option to create a list of valid values. However, in this case, we'll need to use the custom option and write a formula that evaluates to either true or false. If the formula returns true, Excel will allow the value to be entered. If it returns false, Excel will display an error message. The first step is to check if the value entered is an integer. We can do this by using the INT function and comparing it to the original value. If they are equal, then we know it is an integer. Next, we need to check if the value is one of the previously used values. We can use the COUNTIF function to count how many times the value appears in the range of previous values. If the count is greater than 0, then we know it is a valid value. By combining these two checks, we can create a formula that will validate the check number field.

Now, I know what you're thinking - what about the drop-down list? While I don't have a perfect solution for that, I do have a workaround. By enabling the "Auto Complete for cell values" option in the "Tools" menu, Excel will suggest values as you type based on previous entries. This works well if you have a limited number of values, such as in Mike's case with just a few categories. However, if you have longer or similar values, it may not work as effectively. But for this situation, it should do the trick.

So there you have it - a solution to Mike's tricky validation issue. I hope you found this netcast helpful and informative. Don't forget to tune in next time for more Excel tips and tricks from MrExcel. And if you have any questions or suggestions for future topics, be sure to leave them in the comments below. Thanks for watching!

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) Number Validation and AutoComplete in Excel
(00:32) Use of data validation and list of categories
(00:42) Setting up validation for check number field
(00:57) Explanation of formula for validation
(01:10) Limitations of list drop-down
(01:20) Explanation of data validation
(01:30) Use of COUNTIF function
(02:25) Formula for validation
(03:05) Solution for avoiding errors in formula
(03:43) Copying formula for multiple cells
(03:59) Examples of validation results
(04:12) Use of AutoComplete for cell values
(05:01) Clicking Like really helps the algorithm

#excel #microsoft  #tutorial 

This video answers these common search terms:
AutoComplete for check numbers in Excel
Custom list in Excel
Enabling AutoComplete for cell values in Excel
Excel checkbook setup
Excel data validation
Excel tips for checkbook management
List drop-down in Excel
Setting up validation for check numbers
Using the COUNTIF function in Excel
Using the INT function in Excel
Writing formulas for data validation in Excel

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



Mike writes in with a question that is way too hard for a Friday, and way too hard considering it is dawn in Maui. Mike has what sounds like a simple task  a checkbook register in Excel, but needs to create a tricky validation that will allow either an integer check number or one of a handful of values such as ATM, EFT, etc. Episode 491 shows you how to set up data validation to handle this problem.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel