Microsoft Excel Tutorial: Creating a Validation List Without Blanks in Excel | Advanced Technique by Bob Umlas.

Welcome back to the MrExcel Podcast. In this episode, we have a special guest, Bob Umlas, author of the advanced Excel book, Excel Outside the Box. Bob will be sharing a powerful technique called "Validation List No Blanks" that combines various advanced concepts to solve a common problem. This technique is just one of the many valuable tips and tricks found in Bob's book.

Data validation is a useful tool in Excel, but what if the data you want to use as validation has spaces in it? Bob has a solution that doesn't require any macros. By using formulas, he is able to create a data validation range without any spaces. This is done by using the INDEX function to look at the values in column A and return the corresponding values in column I. The result is an array formula that can be filled down to cover all the data.

To understand how this works, let's take a closer look at the formula. The first part of the formula uses the ROW function to return the row number of each value in column A. This is then used to find the smallest value using the SMALL function. The result is a list of numbers that correspond to the rows of the data without any blanks. This list is then used as the source for the data validation range. Bob also explains how to handle any #NUM! errors that may occur in the list.

To make it easier to use this technique, Bob has defined a name called "list" which is an OFFSET function that references the data without any blanks. This name is then used in the data validation formula to create a dynamic range that can be easily updated. This is just one of the many advanced techniques that can be found in Bob's book, Excel Outside the Box. You can purchase the ebook version for just $14.95 on our website, mrexcel.com/outsidethebox.html.

Thank you for tuning in to this episode of the MrExcel Podcast. We hope you found Bob's technique for creating a validation list without blanks to be useful. Be sure to check out his book for more advanced Excel tips and tricks. And don't forget to subscribe to our channel for more helpful Excel tutorials. See you next time!

Buy Bill Jelen's latest Excel book: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]


Table of Contents:
(00:00) Bob Umlas Validation List No Blanks
(00:16) Explanation of Advanced Technique
(00:26) Data Validation on Steroids
(00:36) Bob's Explanation
(01:38) Formula Explanation
(04:23) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
#NUM! errors in Data Validation
Array formulas
Bob Umlas
Data Validation on steroids
Data Validation with spaces
Excel Outside the Box
Formulas for Data Validation
INDEX function
Offset function for list definition
Smallest number formula


Join the MrExcel Message Board discussion about this video at [ Ссылка ]


The source of your data validation list contains blank cells and you don't want those blanks to appear in the dropdown. Bob Umlas combines many advanced Excel techniques to solve this problem. Check out Bob's book at [ Ссылка ]

свернуть