Create Dynamic Data Validation List – Offset Function Method

Basic Excel Tricks Logo New

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:

Excel Tricks

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:

Excel Tricks

 

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:

Excel Tricks

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 😊

Excel Tricks

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:

Excel Tricks

Hope you enjoyed this article. See you soon again with a new article 😊 

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

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