Solved

Display drop down list in Excel

Posted on 2013-01-21
4
324 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
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now