Drop-down list Populated by a Subset of a larger list

I'm trying to create a drop-down list (in Data Validation) that is populated
by the subset of a larger list. The larger list is not sorted, nor can it
be. If possible, I'd prefer not create a subset list elsewhere in the
worksheet and have a list in Data Validation refer to it. Ideally, I'd like
to enter a formula in the Source: box (after choosing List under Allow: in
Data Validation) that would create a filtered list from the larger list.

It's difficult for me to explain this well, but maybe an example will help:

I have a table of baseball players, 20 rows by 3 columns (columns are
Position, Name and Batting Avg.). I'd like to create a pull-down menu, below
the main menu, with just first basemen in it (and let's assume there are 3
first basemen in the larger list).

Any help is greatly appreciated, I've been struggling with this for a while.

Hi!

You have 2 choices:

1. sort the original list then you can write a formula that'll populate the
drop down based on a players position.

2. create sublists of each position.

>The larger list is not sorted, nor can it be.

Kind of narrows down your options! Why can't you create a sorted copy of the
larger list?

Biff

"RJH" <[email protected]> wrote in message
news:[email protected]...
>
> I'm trying to create a drop-down list (in Data Validation) that is
> populated
> by the subset of a larger list. The larger list is not sorted, nor can it
> be. If possible, I'd prefer not create a subset list elsewhere in the
> worksheet and have a list in Data Validation refer to it. Ideally, I'd
> like
> to enter a formula in the Source: box (after choosing List under Allow: in
> Data Validation) that would create a filtered list from the larger list.
>
> It's difficult for me to explain this well, but maybe an example will
> help:
>
> I have a table of baseball players, 20 rows by 3 columns (columns are
> Position, Name and Batting Avg.). I'd like to create a pull-down menu,
> below
> the main menu, with just first basemen in it (and let's assume there are 3
> first basemen in the larger list).
>
> Any help is greatly appreciated, I've been struggling with this for a
> while.

Thanks Biff, I'm using the second option right now: sub-lists of players. In
fact, I'm using a modification of a formula I think you posted elsewhere on
'net! You are prolific!

The first option would be more efficient, but I can't sort the larger list
because: 1) it's a web query and 2) I need the workbook to be dynamic. If
there is a way to create a sorted list dynamically (an array function that
sorts referenced data?), that would be the answer, but I'm not aware of a way
to do that. Any suggestions?

Rich

"Biff" wrote:

> Hi!
>
> You have 2 choices:
>
> 1. sort the original list then you can write a formula that'll populate the
> drop down based on a players position.
>
> 2. create sublists of each position.
>
> >The larger list is not sorted, nor can it be.
>
> Kind of narrows down your options! Why can't you create a sorted copy of the
> larger list?
>
> Biff
>
> "RJH" <[email protected]> wrote in message
> news:[email protected]...
> >
> > I'm trying to create a drop-down list (in Data Validation) that is
> > populated
> > by the subset of a larger list. The larger list is not sorted, nor can it
> > be. If possible, I'd prefer not create a subset list elsewhere in the
> > worksheet and have a list in Data Validation refer to it. Ideally, I'd
> > like
> > to enter a formula in the Source: box (after choosing List under Allow: in
> > Data Validation) that would create a filtered list from the larger list.
> >
> > It's difficult for me to explain this well, but maybe an example will
> > help:
> >
> > I have a table of baseball players, 20 rows by 3 columns (columns are
> > Position, Name and Batting Avg.). I'd like to create a pull-down menu,
> > below
> > the main menu, with just first basemen in it (and let's assume there are 3
> > first basemen in the larger list).
> >
> > Any help is greatly appreciated, I've been struggling with this for a
> > while.
>
>
>

Hi!

It's possible to dynamically sort a list with formulas but I'm not so sure
it would be any "better" than creating sublists as you have done.

Can you give me a more thorough explanation of what you're wanting to do?
Does the web query pull the same amount of data each time or does it vary?

>I have a table of baseball players, 20 rows by 3 columns (columns are
>Position, Name and Batting Avg.). I'd like to create a pull-down menu,
>below
>the main menu, with just first basemen in it (and let's assume there are 3
>first basemen in the larger list).

What data do you want in the drop down, the first basemens names? I'm
guessing that you also want to do this for any position?

We might be able to get this to work!

Biff

"RJH" <[email protected]> wrote in message
news:[email protected]...
> Thanks Biff, I'm using the second option right now: sub-lists of players.
> In
> fact, I'm using a modification of a formula I think you posted elsewhere
> on
> 'net! You are prolific!
>
> The first option would be more efficient, but I can't sort the larger list
> because: 1) it's a web query and 2) I need the workbook to be dynamic. If
> there is a way to create a sorted list dynamically (an array function that
> sorts referenced data?), that would be the answer, but I'm not aware of a
> way
> to do that. Any suggestions?
>
> Rich
>
> "Biff" wrote:
>
>> Hi!
>>
>> You have 2 choices:
>>
>> 1. sort the original list then you can write a formula that'll populate
>> the
>> drop down based on a players position.
>>
>> 2. create sublists of each position.
>>
>> >The larger list is not sorted, nor can it be.
>>
>> Kind of narrows down your options! Why can't you create a sorted copy of
>> the
>> larger list?
>>
>> Biff
>>
>> "RJH" <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> > I'm trying to create a drop-down list (in Data Validation) that is
>> > populated
>> > by the subset of a larger list. The larger list is not sorted, nor can
>> > it
>> > be. If possible, I'd prefer not create a subset list elsewhere in the
>> > worksheet and have a list in Data Validation refer to it. Ideally, I'd
>> > like
>> > to enter a formula in the Source: box (after choosing List under Allow:
>> > in
>> > Data Validation) that would create a filtered list from the larger
>> > list.
>> >
>> > It's difficult for me to explain this well, but maybe an example will
>> > help:
>> >
>> > I have a table of baseball players, 20 rows by 3 columns (columns are
>> > Position, Name and Batting Avg.). I'd like to create a pull-down menu,
>> > below
>> > the main menu, with just first basemen in it (and let's assume there
>> > are 3
>> > first basemen in the larger list).
>> >
>> > Any help is greatly appreciated, I've been struggling with this for a
>> > while.
>>
>>
>>

Sorry to steal an old thread, but I've been looking for an answer to an exel dilemma, and I think, Biff, you might have the answer. I've been looking for a while and this is the closest example to my own of what I want to do---sort of new to excel and not sure how to make it happen though.

Here it is:

I have a list of all my Projects from 2010 in one column, and in the next column they are either listed as active or completed. On another worksheet I have a timesheet with a column to designate the project and then seven columns to designate each day of the week. I want the Project column to have pulldown lists(using data validation) where I would just see "active" projects from my original project list. I can only figure out how to get it to show all the projects. Is there a way to do this?

I sincerely appreciate any help you could provide me with, and thank you in advance for your help!

P.S. Basically I want you to explain option 1 above: sort the original list then you can write a formula that'll populate the drop down based on a players position--except active projects