This free Excel help article on the Microsoft Excel DGET function is a part 2, or continuation of the article, Excel DGET function. Please read the first article so that you may understand the DGET function better and then follow up with this one.
In the previous free Excel help article you learned how to use the Microsoft Excel DGET function to extract a record of data based upon one or more criterion. In this article we’ll more or less be doing the same thing but using a drop-down menu, basically a using a data validation control that will drop-down a list of salespersons.
So we use the same database table as mentioned in the previous article. Remember the cells where we input our DGET functions one by one to extract the data from the record. We now need to insert a validation control in the first cell i.e the leftmost cell so that we can choose a salesperson from the list and the DGETS in the adjacent cells, i.e. one each for Grade, State, Month(Date) and Sales will automatically extract the information for the salesperson. Only one other assumption we make here is that unlike the previous table where a unique salesperson’s record could occur once for every month (and therefore one or more records for the entire year) , here in our table a salesperson’s record appears only once.
To include a list for the salespersons we perform a data validation by clicking Data Validation on the Data Tools group under the Data tab. The Data Validation dialog opens. There are three tabs here, Settings, Input Message and Error Alert. Under Settings you define what type of validation you want to. In our case we want the list of salespersons to be displayed from where the you can select one and for whom the data is going to be fetched by DGET in the adjacent fields. So select List from the list. Under the Source field ensure the range i.e. the list of cells containing the names of the salespersons is entered which specifies where to look for the list of salespersons. You can also select the range of cells by clicking the button of this field. Ensure that the In-cell dropdown checkbox is selected too. Selecting In-cell dropdown ensures that the drop-down list appears inside the cell and a down-arrow head button appears beside the cell. Clicking the arrow-head button will drop down the list. And that is it!
As in the previous Excel help article the rest of the functions are just the same, i.e. inserting the Excel DGET function for each field into the adjacent cells. Now all we need is to choose a salesperson from the list we created and the Microsoft DGET functions will automatically populate the other cells with the information about that selected salesperson.
This type of fetching data is particularly useful when are looking up for information pertaining to individuals such as salespersons in a company, students in a school, patients in a hospital etc., Experienced Excel consultants have seen this done several times over, though DGet is not used that often. The only requisite is that there must be only one record (no duplicates) for each of them in the table from where DGET is fetching data.