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…
How to protect and share your workbook? Creating beautiful and professional dashboards, projects always lead you to success however there are places when you wanted to protect your dashboards, sheets, cells to prevent users to…
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…
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…
How to Hide Ribbon &Formula bar in excel? While preparing many projects, my clients always want me to make professional, good looking dashboards. So I hide few things like Gridlines, Column & Row headings and…
How to Insert Symbol in Excel? I came across many queries regarding inserting special symbols in Excel. Here we are guiding how you may do this quickly in excel. Follow these steps and you may…