Data Validation Drop down List, attaching formulas to drop down list results in Microsoft Excel

Data Validation Drop down List, attaching formulas to drop down list results in Microsoft Excel.

Add data validation to a cell or a range
1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation (bottom right corner).
3. From the drop-down window, select Data Validation.
4. On Validation criteria window, click the dropdown list tab under Allow.
5. Select List
6. Click on the area below Source.
7. Click the cell in your worksheet (first selection for the top of your list) and drag down to highlight the area for your list.
8. Hit OK.

Note: I always select a extra cells at the bottom on my list range. This will allow me to add names at the bottom of my list without having to update the list range in Data Validation.

The dropdown list is a great tool when you want to build a lookup chart for formulas like on a dashboard. This will help the user know the different search options for this lookup table.

In this video I show you two different lookup functions that you can use with a dropdown list. The options for using formulas with a list are endless. Below are the two functions we used in this video.

First, we wanted to use a Vlookup function to find the First sale completed by the sales rep.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Second, we were looking for the total sales by Sales rep. We used a Sumif function to complete this seach.

=SUMIF(range, criteria, [sum_range)

We also changed the search criteria to search by City on our dropdown list.

As mentioned in the video, it is very important that you don’t have multiple names or values in your list. These formulas we used only work for the first value on the list. I always chart out my search list to ensure the search value is unique.

Link to Worksheet

[tomtomooohm]

You have a video about checkboxes. You have a video about dropdown menu. Want to challenge you to show us how to do a dropdown with checkboxes but without repeating in results if checked multiple times :)