Look Who it Was Random video

Excel Dynamic Validation List that Expands with New Entries - Episode 720

2,881 views · Published 13 February 2009 · 3:16 · Indexed 4 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Allow Validation List to grow in the future.

Welcome back to the MrExcel netcast! In today's video, we're going to be discussing dynamic validation in Excel. This is a question that was sent in by Jonathan, who was having some trouble with data validation and wanted to know how to set it up in a way that would allow for a growing list.

Over the past few weeks, we've been diving into the topic of data validation, and this is just another aspect of it. Jonathan's specific issue was that he had a list with 16 items, but he wanted to leave room for more items to be added in the future. However, when he set up validation for a larger range, it caused issues with the scroll bar.

So, how do we solve this problem? Well, the solution involves setting up a named range using the OFFSET function. This function takes 5 arguments and allows us to create a range that can automatically adjust as the list grows. We start by inserting a name and defining it with a formula that points to the range we want. Then, we use the COUNTA function to count the number of entries in the list and set the range to be 1 column wide.

Once we have our named range set up, we can easily use it for data validation. This allows us to have a drop-down list that only includes the items in our list, without any extra blank cells or issues with the scroll bar. And the best part is, if we add a new item to the list, the drop-down will automatically update to include it.

Another benefit of setting up a named range is that we can store all of our lists on a separate worksheet. This means we can set up validation on a different worksheet, which is usually not possible. And if we want to hide the back worksheet, we can easily do so to prevent anyone from seeing it.

So, thank you to Jonathan for sending in this question and thank you for tuning in to this netcast from MrExcel. I hope this video helped you understand how to set up dynamic validation in Excel. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. 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 question from Jonathan about data validation
(00:12) Recap of previous discussions on validation
(00:22) Jonathan's specific situation with a growing list
(00:42) Explanation of OFFSET function and its 5 arguments
(01:00) Creating a named range using OFFSET function
(01:47) Setting up validation using the named range
(01:57) Demonstration of the drop-down list automatically growing
(02:14) Additional benefits of using a named range for validation
(02:54) 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:
Back worksheet
COUNTA function
Data validation
Data validation source
Drop-down list
Lists worksheet
Named range
OFFSET function
Scroll bar
Validation

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

Jonathan is frustrated that his validation scrollbars start in the midst of several blank cells that he set up in his validation list. He was doing this to allow room for future growth in the list.

Instead, Episode 720 will show you how to set up a dynamic named list using the OFFSET function.

More from this channel