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 Create a Pivot Table? Pivot Tables in excel is backbone of data analysis in excel. If you want to perform various analysis in excel, then I would suggest, learn this amazing excel feature…
In layman language, this chart divides the data into multiple range groups with same interval i.e. 1-5, 5-10… and then plot the data into these groups to find the distribution among these group. So that you may understand, what are the major areas which has large impact or your major data fall. This helps the user to understand the nature of your data
Watch Excel Tutorial Video – How To Create Dropdown List In Excel How to Create a Dropdown list in excel? Microsoft Excel is what most professionals are using for their day-to-day office. Creating a drop-down…
Pie Chart is one of the ways of visual presentation of your data sets. Sometimes it makes easier to understand the data while visualizing.
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…
Lock Cells to avoid editing, Hide Formulas Sometimes you create amazing projects, dashboards however people can make mistakes and edit the calculations, formats what you have made. Still you can protect the sheets, workbooks and…