Hi Keith

I think you will need to use some Array formulae to achieve our 2 lists.
Whether these, on a list of 5000 names will be too slow for you, you will
have to decide.
I copied your data into columns A and B, starting at Row 2. Row 1 had
headers of Name and Status

Create 2 named ranges. InsertNameDefine
Name Names
Refers to =$A$1:INDEX($A:$A,COUNTA($A:$A))
Name Status
Refers to =$B$1:INDEX($B:$B,COUNTA($A:$A))

In C1 enter the formula
=COUNTIF($B:$B,TRUE)
In D1
=COUNTIF($B:$B,FALSE)

In C2, and copied down as far as required, enter the Array formula
{=IF(ROWS(C$2:C2)<=C$1,INDEX(Names,SMALL
(IF(ISNUMBER(SEARCH(TRUE,Status)),ROW(Status)),
ROWS(C$2:C2))-MIN(ROW(Status))+1),"")}

To enter or modify an Array formula, use Control+Shift+Enter (CSE) not just
Enter.
Do not type the curly braces { } yourself. If you use CSE, Excel will
automatically insert them.

In D2, and copied down as before
{=IF(ROWS(C$2:C2)<=C$1,INDEX(Names,SMALL
(IF(ISNUMBER(SEARCH(FALSE,Status)),ROW(Status)),
ROWS(C$2:C2))-MIN(ROW(Status))+1),"")}

You can then create the lists for your DV as Trues (you can't use the name
True)
=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Sheet1!$C$1+1)
and Falses
=Sheet1!$D$2:INDEX(Sheet1!$D:$D,Sheet1!$D$1+1)

--
Regards
Roger Govier

"ker_01" wrote in message
...
Soooo close- I added two columns to my data sheet, so the name will show
up
in C or D depending on the value in B

Sasha True Sasha
Bobbi False Bobbi
Carl False Carl
Gus True Gus
Drac False Drac
Claire True Claire

I had this flash of inspiration, thinking I could then use those columns
to
feed my two data validation lists, using the "ignore blanks" so that I
would
have just the lists of names. Unfortunately, it appears that "" doesn't
constitute a blank from the data validation perspective - I guess the
presence of a formula (even one that returns "") is enough to keep blank
placeholders in the data validation list.
Does anyone know if there is a way around this?
Thanks!


"ker_01" wrote:

I have a data sheet with 5000 items, and a user sheet with three
drop-down
cells (data validation/list). When the user selects the first list, I
want to
use that value to populate the second two lists. I've set up formulas so
that
I know which rows (value in column A) *should* be in each of the two
child
listboxes (all "true" rows go in list 1, all "false" values in list 2)
based
on the user's first selection. What I haven't figured out is an efficient
way
to grab 5000 values in Column A based on a value in another column, and
actually transform that into my two data validation lists (efficiently)

Example with 6 records:
Sasha True
Bobbi False
Carl False
Gus True
Drac False
Claire True

All 5000 records need to end up in either list 1 or list 2, and still
have
the workbook responsive enough to be usable (not spending extensive time
recalculating).

Any ideas on how to use the true/false values in column B to populate the
data validation lists, as efficiently as possible? I can't use VBA
because
the workbook will be widely distributed and not all users will enable
macros.
Based on the level of user sophistication, I'm also avoiding things like
having them switch to the data sheet and sort it after making their first
selection- I'm really looking for a formula/named range formula/ data
validation formula type of approach, but I'm at a loss for how to get
these
non-contiguous values into the data validation list.

Thank you!!
Keith