Create Dynamic Data Validation List
When I started my career in working with excel dashboards, I always used to face most common challenge in “Data Validation” technique where I want a smart data validation to avoid all blank cells and keep adding and deleting the values from drop down dynamically.
Here we want to talk on an amazing trick to create data validation and avoid blank cells without changing the range. So let’s start with these simple steps:
Step 1: Identify the range which you want to show into data validation list. Here I selected a range from A2:A15. Refer the below screenshot:
Step 2: I will be creating a dynamic drop down list using above Range (A2:15) and applying this validation to Cell “C4” as below:
OFFSET FUNCTION
Step 3: Here we will be using “OFFSET” function to create dynamic drop down list:
“=OFFSET(reference, rows, cols, [height], [width])”
Note: Click here “OFFSET” to learn this function in detail
Parameters:
Reference: This is a range which we will be using and adjusting. So here it is “$A$2:$A$15”
Rows: As our base row starts from “2” and our data also starts from $A$2. Therefore, we should not be using this parameter
Cols: As we will be adjusting the same column and no need to shift data anywhere on the sheet Therefore, we should not be using this parameter
Height: We are going to adjust the height of our data in current example which starts from $A$2 and ends at $A$15. So it depends on the data filled. Currently we have data till 8th Row. We will calculate the height until filled cell by using COUNTA function
Note: Click here “COUNTA” to learn this function in detail
Width: We do not have width of the data, so can leave this as well
So below is our final formula: “=OFFSET($A$2:$A$15,,,COUNTA($A$2:$A$15),)”
Step 4: Select “C4” cell and create “Data Validation” list by pasting the formula under “Source” as below:
Click “OK”
Note: You can create a named range by using the same formula and then may use that named range instead of pasting the formula under data validation list
Step 5: Here is your Validation list 
Note: Click here “Data Validation” to learn this feature in detail
And once you add a new value in “A9” cell, it will automatically added to drop down list as below:
Hope you enjoyed this article. See you soon again with a new article
Add Your Heading Text Here
Secrets of Excel Data Visualization: Beginners to Advanced Course
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Excel VBA Course : Beginners to Advanced
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Use Automated Excel Utility Tools to increase productivity
- Quickly Move or Delete Files or Folder >> Click here
- Handle Duplicate Files with Automated Utlity >> Click here
- Generate Dummy Data For Testing >> Click here
- Excel Files and Sheets Consolidator >> Click here
- Manage Outlook Emails efficiently >> CLick here
- Send Bulk Emails wiht our Excel Automation >> Click here
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
