Excel Drop Down List From Database
Responding to queries from The Knowledge Base e-support service, Simon Hurst has produced a simple step-by-step tutorial showing how to create drop-down menus in Excel and how to link them to an Access database. This article is a summary version of the full tutorial, which you can download as a Word document.
The question
This item is a response to two clients' requirements. One asked about the best way to enter data into cells by selecting from a drop-down list, whilst another wanted to enter information into a spreadsheet from an Access database.
In this example you will look at a couple of ways of including drop-down lists, and then show how to make a drop-down list include items from a remote data source.
Drop-down list 1 - form control
Perhaps the most obvious way to create a drop-down list in Excel is to include the appropriate form control. First of all you will need to display the form control toolbar. To do this, select View-Toolbars and then choose the Forms toolbar. Select the Combo Box tool, and then draw a horozontal shape starting in cell C3 for a Combo box on your worksheet.
Format control properties
Now you have a drop-down list, but it doesn't do a great deal yet. In order to make it do what you want, you need to set its properties. Right-click the control and choose the Format Control option:
The Combo box form control works by displaying the items held in a range of cells somewhere in the workbook ' the Input range. When one of the items from the list is selected, the control enters the position of the chosen item into a nominated cell ' the Cell-link. Try entering a list of items (for example 'Penguin', 'Pelican', 'Parsnip', 'Pheasant' and 'Partridge') in cells A5 to A9 and set the Input range to those cells ("$A$5:$A$9" in the input panel). Then set the Cell link to A3.
However, if you choose the third item (eg 'Parsnip') the number 3 will appear in cell A3. This is because 'Parsnip' is the third item in the list. If you wanted to include the word 'Parsnip' itself, you would need to use the Index function to look up the actual value of the third item in the list. To do this, enter the following formula in cell A2:
=INDEX(A5:A9,A3)
The range of indexed cells is set to your list, and the 'row number' is set to the Combo-box linked cell, A3. As you choose an item from the Combo-box, the value in A3 will change to the position of that item within the list, and then the formula in cell A2 will use the Index function to return the correct item in the list. So if you choose 'Penguin', the first item in the drop-down list, the number 1 appears in cell A3, and the Index function returns the value 'Penguin' in cell A2.
This method works, but it is all rather complicated and long-winded. And as it stands, it only allows you to choose the contents of a single cell. Fortunately, there is an easier way.
Drop-down list 2 - Data validation
The Data Validation menu may seem a rather strange place to go to create a drop-down list, but the menu selection includes a List option that should do just what you want.
Select the cell or range of cells that you want to enter from your list and then choose Data-Validation. From the Allow pull-down menu, choose the List option and then set the Source box to the range of data that contains your list ' A5:A9. Note that the 'In-cell dropdown' option is selected.
If you set the data validation properties for the whole of column E, all you need to do is click any cell in that column, and a drop-down appears, allowing you to select an from the list.
There is one slight irritation affecting this method of setting up a drop-down list ' the range of cells containing the list must be on the same sheet as the validation cells. This limitation does not exist for the form control method. However, by allocating a 'range name' to the range of cells to be used you can overcome this problem and use a 'range name' referring to a different sheet as the source for the Data Validation list.
Using Microsoft Access as a data source
Both the drop-down menu methods link to a range of cells somewhere in your Excel workbook. If you need to, you can set this range to be an external data range that could be linked to data in an Access database, for example.
In the following example, you will link the Data-Validation drop-down to an external range that will bring in the company names from the customers table of the 'Northwind' database. This database is the one that is provided as an example with Microsoft Access.
Linking to the Access data
First of all you will create the link to the Access data. Delete the contents of column A, and position the cursor at cell A5, where the old Pheasant/Parsnip list started. Then choose Data-Import external data-New Database query. [In Excel 97 and 2000: Data-Get External Data etc]
Choose MS Access Database and then find the Northwind database ' the location will vary according to the version of Access in use, and the way the system was installed, but a good place to start is for a directory such as:
C:\program files\microsoft office\office10\samples
Having selected Northwind.mdb, you are then presented with a list of the tables in the database. You want to use a field from the Customers table, so you click the plus sign next to that table to display the individual field names. You can check the contents of a field by clicking on it and then clicking the Preview Now button. Click and preview the CompanyName and you should see a list of the customer names.
Once you have established that this is the field that you want to use, click on the single right chevron button to add it to the Columns in your query list.
You could go on and add further fields, including fields from other, linked, tables but in this case this is the only field you need. Press the Next button to go through the various screens of the Query Wizard. You don't need to filter the data, but you will sort by CompanyName to ensure the list is in alphabetical order:
After you finish with the wizard, you will see a screen that lets you choose where you want the list to appear, and also allows you to set the properties of the data range. Before clicking OK, click the Properties option to make a couple of changes to the properties. In the top Name box, type in 'ClientName' and turn off the 'Include field names' option (the first item under the Data formatting and layout heading).
Then click OK and OK again to return the data to the Excel sheet. Now select column E, and go to Data-Validation and set the Source for your List to:
'=ClientName'
'ClientName' being the Name you gave to the External data range in the previous step.
Now you can see the drop-down list includes all the items from our external data range.
Setting the form control 'Input range'
The form control method can also use the external data range. By right clicking on the Combo box control and choosing the Format control option, you can get to the Control. tab (on the right hand of the five options). This tab optoin inlcudes an Input range box where you can enter the name of your external data range.
Conclusion
You have placed the external data range in a visible part of the worksheet in order to demonstrate how it works. It would generally be preferable in the case of the Data-Validation drop-down to place it elsewhere in the worksheet to avoid confusion, and in the case of the Form control, to use a completely separate sheet for the input ranges.
As mentioned earlier, using a separate sheet is not an option for the Data-Validation method since the data source must be on the same sheet as the validation cells whether a simple list, or an external data source, is used. However, if you create the list first on a separate sheet, and allocate a range name to it, you can then link the list to this range name even though the area it relates to is on another sheet. This does not seem to work directly for the name of an external data range. Instead, you need to define a 'range name' for the external data range in order to use it in this way.
About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping practitioners make effective use of technology. He is also the author of AccountingWEB's Office ProductivITy Kit and '100 Best Tips', visit the The Knowledge Base website. AccountingWEB members can also access his back catalogue and IT Clinic online email support via the Office ProductivITy service.
AccountingWEB's ExcelZone publishes a wide variety Expert Guides on integrating Excel with database applications, some of which are listed below. If you would like to be notified when new tutorials appear, click the button on the right to subscribe to our fortnightly ExcelZone newswire. The subscription routine will take you to the AccountingWEB home page when it's finished.
Related articles
Excel Drop Down List From Database
Source: https://www.accountingweb.co.uk/tech/excel/excel-clinic-link-drop-down-menus-to-ms-access
0 Response to "Excel Drop Down List From Database"
Post a Comment