Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions
 Exploring Excel   
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     

Home > Excel Solutions

Look Up Key Data with an
Excel Dropdown List Box

"Iím trying to create a form for tracking the hours that I work and I want it to include the travel time to companies. Ideally Iím looking for a drop down list which, once I select a company, Excel will apply the transit time to a different column." -- Robbie B.

Robbie,

This is a common need. So I'll offer a general explanation.

Here's a simple Excel 2003 version of what I think you're looking for:

And here's a version from Excel 2007:

In the version from Excel 2003, the gray border rows at the top and bottom of each table help to ensure that your formulas continue to work as you add rows to your tables. In Excel 2007, the new Table features eliminate the need for the border rows.

In either version, you probably would maintain the Travel Times table in one  sheet of your workbook, and your Working Hours table in another worksheet of the workbook.

Dropdown lists in each cell in the Place column of the Working Hours table let you choose which place you've traveled to. When you make a selection, the travel time appears in the Travel Time column. No macros are used.

To set this up, first create the Travel Times table as shown in either figure. In Excel 2003, be sure to set up the gray border-rows as shown. In Excel 2007, don't worry about the color formats, just enter your text.

Select the range from your column labels to the bottom of your table. Choose Ctrl+Shift+F3 to launch the Create Names dialog. Make sure that only Top Row is checked. The choose OK.

Or, if you want to use the menus to access Create Names...

...In Excel 2003, choose Insert, Name, Create.

...In Excel 2007, choose Formulas, Defined Names, Create From Selection.

In Excel 2007, take the extra step to set this figure up as a Table. To do so, make sure that the range F3:G7 still is selected, then press Ctrl+T. Or, if you want to use the menus, choose Insert, Tables, Table.

When you create the table, Excel will add a table format that you easily can change.

Set up the Working Hours table as shown in either figure. Assign the range names as with the first table. In Excel 2007, define the figure as a Table.

When Excel 2007 creates a Table, it automatically assigns Table Names like Table1, Table2, and so on. We need to change the name to something more useful. Therefore, select any cell in the Travel Times table. Choose "Table Tools" in the title bar above the row of tabs. Doing so will display the Design contextual tab for Tables.

In the Design tab, choose the Table name edit box in the Properties group. (It probably will be "Table1".) Change this name to "TravelTimes" (without the quotes, of course). Similarly, change the name of the second table to "WorkHours".

The next step is to create the list box shown in column C in the figures above or below. To do so, select the area in column C where data will be entered. Then...

...in Excel 2003, choose Data, Validation.

...in Excel 2007, choose Data, Data Tools, Data Validation.

In the Settings tab, choose List in the Allow dropdown list box.  In the Source edit box, enter:

=Places

Then choose OK.

(Note that "Places" is the name we assigned to the range of places in the Travel Times figure. )

With the data validation in place, Excel displays the control for the dropdown list box whenever you select a cell in the data area of the Place column in the Working Hours figure.

You can see these controls in the two figures below, which are repeated from above.

To add the Travel Time data to the Working Hours figure...

...In Excel 2003, enter this formula in the cell shown:

D5:  =IF($C5="","",INDEX(Times,MATCH($C5,Places,0)))

Now copy this formula down the column in the figure.

...In Excel 2007, enter this formula in the cell shown:

D4:  =IFERROR(INDEX(Times,
           MATCH(WorkHours[#This Row] [Place],Places,0)),"")

Here, enter the formula in one line, of course. After you do so, Excel will automatically copy the formula down the column of the Table.

This formula uses two new features of Excel 2007. First, IFERROR uses this syntax: =IFERROR(value,value_if_error), which is self-explanatory. Second, the MATCH function uses this reference for cell C4:

WorkHours[#This Row] [Place]

Notice that space here between "[#This Row]" and "[Place]". That space is necessary, because it tells Excel to return the value found at the intersection of the current row in the WorkHours table and the range named "Place".


To use either Working Hours figure, just enter information in the Person and Place columns. Whenever you choose a Place, Excel updates the Travel Time from the table.

To add rows to the tables...

...In Excel 2003, insert rows immediately above the bottom border and then copy the formula in the Travel Time column downward as needed.

...In Excel 2007, merely add new information to the first row below the table. When you do so, Excel automatically expands the table to include the new data. Excel also copies any formulas into that new row as necessary.

All the best,

Charley


 
 
 
 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright © 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

Excel Dashboards

Create professional quality dashboard reports with Excel.