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.

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

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" wrote in message
...

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.

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

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" wrote in message
...

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.

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

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" wrote in message
...
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" wrote in message
...

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.