Display drop down list in Excel

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
tomfolinsbeeAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Sorry, here is the latest:
airport---airline-table.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
tomfolinsbeeAuthor Commented:
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
 
tomfolinsbeeAuthor Commented:
Thank you teylyn!
0
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.

All Courses

From novice to tech pro — start learning today.