Dropdown list based on two criteria

Hi,

Thanks in advance for any thoughts or assistance you can provide. I am trying to create a drop down list that is dependent on two criteria. To depict the issue, I've created a mock-example about sports.

On my 'data' sheet:
Column 1: Name of sport
Column 2: City
Column 3: Name of sporting team

On my 'form' sheet:
Cell 1: Dropdown list to select name of sport
Cell 2: Dropdown list to select city
Cell 3: I want this cell to be a dropdown list of names of sporting teams, based on the conditions set by Cell 1 & 2.

Example is attached.

Thank you.

Thanks for your response Alan.

The method you suggested would require to keep the data organised in lists. I am rather hoping for a solution that can dynamically search a large spreadsheet of 500 rows for the rows that meet the criteria.

For example, if I were to select the name of sport and city, I'd like the third dropdown list to search through 500 rows for all the names of sporting teams that meet that criteria.

Any further thoughts?

Cheers!
Brendan

Hi Jeffrey,

I don't think that's it either. That solution requires me to keep the lists of data in columns.

Each row of data in my spreadsheet will contain ~20 columns, each specifying some detail about the sporting team.

I am hoping that the the data validation can dynamically search through the rows to extract a list of teams that match the criteria defined by the two previous drop down lists.

Otherwise, I'll need to keep the lists in columns, as well as the data in rows in another spreadsheet.

Thanks again,
Brendan

Originally Posted by jeffreybrown

Another option...

http://contextures.com/xlDataVal15.html

Yes, great, this is something I can work with. I'm going to try knock this out on my own from here.

Thank you very much.

Originally Posted by jeffreybrown

I have to run to the airport, but chech this out and see if it could work for you. I'll be back in about an hour.

It uses VBA and on the Form tab click on the button and the advanced filter will extract unique records and populate a dynamic named range.

I know it needs some more work but need to see if this is something you can work with?

Hi Martin,

I am OK with the city not being dependant, on sport. The important factor is having team name being dependant on sport&city.

Thanks, I'll look at this properly later today.

Brendan

Originally Posted by martindwilson

you can do it like this no code required ,not quite what you said as cities are not dependent on sport but i'd assume most sports are played in most cities anyway

Originally Posted by martindwilson

i've revised it a bit to auto get cities/sports

Hi Martin,

Thanks for the addition.

Just wondering, can you briefly explain the purpose of "Sheet1"?

Cheers.

hii all,

anybody can help me, how to create drop down list with keeping sequel number list
i attach my file...

i appreciated, thank you..

sorry...boss..