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.
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:
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
https://youtu.be/HmJL_y93pAs WEEKNUM function helps to calculate the week number of the given date in a year. It considers 1st January as first week by default and through the output for the given input date. Syntax:…
Group worksheets in Excel makes it easier to manage a big workbook. This feature allows you to treat multiple worksheets as one, helping you perform identical tasks like formatting or data entry across several sheets…
XLOOKUP can find a value that matches exactly or is the closest approximation. It can also look to the left, right, or both, which is an alternative to the VLOOKUP function
This video will help you to understand how you may use conditional formatting to highlight row based on conditions. Subscribe us for more updates
“NETWORKDAYS” function is very helpful feature in the Microsoft excel to calculate the working days from a particular period excluding “Saturday and Sundays”. NETWORKDAYS function subtract the Start Day from the End Date provided.
This feature is particularly useful for summarizing data or improving the readability of complex sheets.