I’m going to show you, how you can combine data validation lists a little drop down menu with functions inside of Excel making the function more interactive and more dynamic.
I’m going to go back to the list functions worksheets, that we were on earlier, where we created the subtotal the DSUM, DAVERAGE and the DCOUNT functions.
We talked about DSUM earlier, where I had the sum of the Total sales column based on the category criteria of Rents and all this other stuff.
But the criteria section, it’s up to the users to know what to put there and how to spell it.
So if I misspell something. I’ll sit here and I’ll look at it as like what is going to wrong, why is it’s not working. Then I’ll eventually I’ll realize that I misspelled. Even a space something you can’t visually see right at least easily.
I want some place to make this more intuitive and more interactive for the users. When someone goes to that cell, we’ll give them a dropdown with all of the options for them to pick from.
I’m going to create a list of the categories the user can pick from.
First, I got my little list.
Now with those values, I’m going to go back to Data validation.
I’m going to change it from allow any value to list and this time, I’m going to Source to read that range of cells M4 to M8, wherever your list is that. then hit OK.
And I’ve now got a little dropdown list, where I can pick the different values and it would update my formulas, that is just so cool.
Nice interactive little drop down menu. Users can pick from there. I’m not relying on me to spell it correctly.
Not to look at the list and as I go What can I search for. You got a little dropdown, where you can pick the criteria that you want and we can apply. Applying data validation, Nice little pick list to a formula make it a more interactive and more dynamic and more usable for our users.