software

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.

gt;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

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

quot;Biffquot; wrote:

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

Hi!

It's possible to dynamically sort a list with formulas but I'm not so sure
it would be any quot;betterquot; 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?

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

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