Display drop down list in Excel

Posted on 2013-01-21
Medium Priority
Last Modified: 2013-01-21
Hello Experts

I would like to add drop down lists to the attached model.

1. Excel displays drop down list in Calculation!B9 containing Airports. Airports are listed on on the Air Freight!B1:B5

2. User selects Airport from the drop down list in B9

3. Excel displays drop down list in B13 containing Airlines that fly to the selected Airport. The list is on the AirFreight sheet.

4. User selects the desired Airline.

Thanks for your help.
Question by:tomfolinsbee
  • 2
  • 2
LVL 50
ID: 38803387

create a range name with Formulas > Name Manager and call it Airport. The reference is

='AIR FREIGHT'!$B$2:$B$5

Then create a range name called Airlines with the formula

=INDEX('AIR FREIGHT'!$N:$N,MATCH(Calculation!$B$9,'AIR FREIGHT'!$M:$M,0)):INDEX('AIR FREIGHT'!$N:$N,MATCH(Calculation!$B$9,'AIR FREIGHT'!$M:$M,1))

In the cells use the range names for the data validation list source.

NOTE: the Airport/Airline table must be SORTED by Airport for this solution to work.

Also, if you want to clear the Airline field when the Airport changes, you will need a macro.

see attached.

cheers, teylyn

Author Comment

ID: 38803441
Thanks tehlyn,

I see the first drop down list in B9 for selecting the Airport, but not the second one in B13 for selecting the available airlines, based on the airport selected in B9.

I'll give the instructions a try.


LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38803453
Sorry, here is the latest:

Author Closing Comment

ID: 38803768
Thank you teylyn!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question