Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to optimize? large non-contig cell range for data validation l
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to optimize? large non-contig cell range for data validation l
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to optimize? large non-contig cell range for data validation l
ker_01,
It sounds like this is what you're looking fo http://www.contextures.com/xlDataVal02.html HTH, Conan Kelly "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to optimize? large non-contig cell range for data validation l
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to optimize? large non-contig cell range for data validati
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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to optimize? large non-contig cell range for data validati
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 InsertModule 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. -- Regards Roger Govier "ker_01" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Can the data validation list range of one cell be driven by theco | Excel Discussion (Misc queries) | |||
Excel 2007 hangs on any change to large range of data | Excel Discussion (Misc queries) | |||
Graph with large data range (hash marks on axis) | Excel Discussion (Misc queries) | |||
Ploting data with a large range including negative numbers | Charts and Charting in Excel |