How to optimize? large non-contig cell range for data validation l

[ker_01]

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

 

[ker_01]

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!

 

[Conan Kelly]

ker_01,

It sounds like this is what you're looking fore:

http://www.contextures.com/xlDataVal02.html

HTH,

Conan Kelly

 

[Roger Govier]

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. Insert>Name>Define>
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)

 

[ker_01]

Roger-

First of all, amazing solution. Would not have figured this out on my own.

The downside is that on my PC, when I change the initial user-defined value
(which triggers the change in the two child lists) it takes over 2 minutes to
recalculate, which won't work for the average end user. I'm looking at ways
to reduce my list of values, although any reductions won't be significant.

I'll post a new thread about concatenating two separate ranges to populate a
data validation list. I could swear that I've concatenated non-contiguous
ranges via named ranges in the distant past, but the syntax isn't coming to
me. If I can do that, I have an idea that might cut the calculated part of
the list in half, which would hopefully result in a 50% increase in response
time.

Thanks!
Keith

 

[Roger Govier]

Hi Keith

If you are able to use macros, then this will be a faster solution for you.
This places a list of Trues in Column D and Falses in column E. Change the
column letters (and numbers in the cell ref) if you want to place elsewhere.

Sub MakeLists()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim lr As Long, i As Long, j As Long, k As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
j = 2: k = 2
Columns("D:E").ClearContents
For i = 2 To lr
If Cells(i, 2) = "TRUE" Then
Cells(j, 4) = Cells(i, 1)
j = j + 1
Else
Cells(k, 5) = Cells(i, 1)
k = k + 1
End If
Next

lr = Cells(Rows.Count, "D").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Trues", RefersToR1C1:= _
"=Sheet1!R2C4:R" & lr & "C4"
lr = Cells(Rows.Count, "E").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Falses", RefersToR1C1:= _
"=Sheet1!R2C5:R" & lr & "C5"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run

You could place a button on your sheet and assign the macro to it, or you
could have the macro called by some event code on the sheet.