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 2: I will be creating a dynamic drop down list using above Range (A2:15) and applying this validation to Cell “C4” as below:
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
How to Compare TWO Columns in Excel? Comparing Columns in Microsoft excel is much often feature that is used while managing database. We have many ways to compare the columns in excel and can be…
This video will help you to understand how you may use conditional formatting to highlight row based on conditions. Subscribe us for more updates
What is Pareto Chart? Pareto principle was introduced by Italian Economist Vilfredo Pareto. He stated that 80% of the effects are caused by 20% of the causes. So if we closely monitor and solve 20% of…
While starting Excelsirji.Com, it is always been critical for me to find the best to amaze the viewer experience. So I spent many hours on web to read, explore amazing excel content which I really…
How to find duplicates in excel? Hope you read the post “Remove Duplicates in Excel”. Now I am going to explain how you can find these duplicates. There are multiple methods available to Find and…
Remove Duplicates in Excel Highlight/Select the Column A, similar to shown in below screenshot: Go To “Data” tab on Menu Bar and click on “Remove Duplicates” as shown below: Once you click on “Remove Duplicates”,…