Over the next couple of weeks, I will be building a dynamic salesman dashboard that allows the user to interact with the spreadsheet to get the information that they need. This post will first focus on building a data validation list that will immediately make the spreadsheet interactive.
The Excel data validation list is a slick tool that allows the user to select a value from a cell based drop down list. The user cannot write/type any other value than what is available in the drop down list (hence the reason they call it data validation) otherwise they will be prompted with an error message box. In this post we will be looking at how to make our validation listing dynamic by allowing the values to change as our referenced data table expands. We will be using a table of salesman data where our validation list will refer to the list of salesmen. As a new salesman is added to the table, the validation list will automatically include the new salesman.
USING A NAME RANGE AND THE OFFSET FORMULA TO BUILD A DYNAMIC VALIDATION LIST.
Name ranges can come in handy when you need to refer to certain ranges of information using formulas and other Excel tools . Let’s look at how we can use the offset formula within a name range to make a validation list dynamic.
Once you’ve entered the formula, simply click the cell button just after the entry box for the formula and then click OK. As a quick note and to not cause any confusion, the caption of the pop-up box will be “New Name” as opposed to “Edit Name” in my scenario above. The reason for this is because I’ve already built my name range and I’m simply going into the name range to show you how to do it.
EXPLAINING THE NESTED OFFSET COUNTA FORMULA
OFFSET(reference, rows, cols, [height], [width])
The offset formula/function is used to identify a starting point or cell/cell range (reference) and from there return the value of the cell that is a specified number of rows (down or up) and columns (left or right) from that reference/starting point. As a simple example, below you’ll see that our starting point is A3, we then move down 5 rows and right 3 columns. This then gives us our value of $68,605.
You’ll notice that the syntax of the offset function has a height and width option. These do not need to be used, but in the case of setting up our name range, we’ve used them. The height will be used to tell us how many cells high we want our return values to be and the width tells us how many columns to the right we want to return values for. This is where the COUNTA formula comes in.
NESTED OFFSET COUNTA FORMULA
OFFSET(SALES_DATA!$A:$A,2,0,COUNTA(SALES_DATA!$A:$A)-1,1)
The formula above is what I used to build my name range in my actual spreadsheet example. As noted here, I’m using the entire A column ($A:$A) from the sales data tab as my reference/starting point. The 2 in the formula is simply stating that we must go down 2 rows from cell A1, and then move 0 columns over. This formula without the COUNTA formula would return a value of “SALESMAN” which is the title of the column (the value of cell A2). This is where we then add the COUNTA formula which says from this location (cell A2) I want the height to be the value of the entire A column (meaning include A2 and all other rows in the A column that are available) then subtract 1. The COUNTA formula counts and adds up any row that actually has a value in column A (in our case we have 15 – this includes the salesman header and the all sales). So the value of the COUNTA formula would be 15 and then we would subtract 1 which would give us 14. This means we will display the 14 cell values below cell A2. Finally, the 1 looks at the width (number of columns we would like to display). In our case we only want to show values in column A therefore, the value for the with will be 1.
The COUNTA formula is what allows us to make this formula dynamic because anytime a new record is added to column A, we add it to our count which changes the height of our offset function.
Now that we’ve setup our new name range called SALESMAN with the offset formula, when we build a data validation list, we will only need to refer to this name range. The offset formula for the name range has made this range of cells dynamic in that now anytime we add a new record to our salesman table, it will also show up on our data validation list.
Now let’s have a look at how to build the validation list.
Now that the dynamic data validation drop down list is completed, we’ll be able to use this in subsequent posts to build our dashboard around which value is select by the user. In our up and coming posts we’ll look at how to use lookup functions to outline some key data points and start building our salesman dashboard. Be sure to sign up to my RSS feed so that you’re notified when my new posts come out.
Until the next time!
Download the Free Excel Workbook!
GET IT FREE! DOWNLOAD INSTRUCTIONSThe Excel file is indeed free. When you click the 'GET IT FREE' button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you'll need is your email address!
Trackbacks/Pingbacks