Solved

Display drop down list in Excel

Posted on 2013-01-21
4
353 Views
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.
airport---airline-table.xlsx
0
Comment
Question by:tomfolinsbee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 50
ID: 38803387
Hello,

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
airport---airline-table.xlsx
0
 

Author Comment

by:tomfolinsbee
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.

Cheers,

Tom
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 500 total points
ID: 38803453
Sorry, here is the latest:
airport---airline-table.xlsx
0
 

Author Closing Comment

by:tomfolinsbee
ID: 38803768
Thank you teylyn!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

691 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