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:

dynamic data Validation list

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:

create dynamic data validation list

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:

Dynamic data validation list

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.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *