Dropdown nightmare!
Posted by Andy Gee on December 11, 2001 12:35 AM
I have a columb with 1000+ dropdown boxes stacked above each other, each dropdown needs to get it's content from one of 8 arrays depending on the value of another cell in the row.
Is there a way in which to make a dynamic array reference cell? ie.
=If(A1=1,"D1:D10",If(A1=2,"E1:E10",If(A1=3,"F1:F10",0))) (OR SOMETHING SIMILAR)
Then use this text output to feed into the input range of the dropdown box???
Can dynamic drop down boxes be made? Will I need VB script? I can only edit VB, I have no idea where to start with this though. Any help would be much appreciated.
Posted by Colo on December 11, 2001 3:22 AM
Hi, It is possivle if use VBA like this.
This is a sample. Please Try.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then Exit Sub
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=1, Operator:=1, _
Formula1:="=" & Cells(1, Range("A1").Value + 3).Resize(10).Address
.IgnoreBlank = True
End With
End Sub
Posted by lenze on December 11, 2001 7:31 AM
Hi Andy: This can also be done without VBA. Name each of your arrays (ie Array1,Array2,etc.) Then choose Insert Name and type in a name(Say Source). Then in the refers to field, enter CHOOSE(A1,Array1,Array2,Array3,etc). Now validate your dropdowns with "=Source" You might also use MATCH and/or INDEX instead of CHOOSE Function
Posted by Andy Gee on December 11, 2001 8:29 AM
I see how it works but I would need to define 1000+ names? Can I use relative references in names??? I dont see how as they seem to be 'virtual'.
Do I have to define all those names?
Any further help would be appreciated greatly.
Posted by Andy Gee on December 12, 2001 8:13 AM
I used a pick list combined with offset to select the columbs deeper into the array!
Thanks for all of your help.
PS. This method also makes it simple to make a dropdown's content relative to another dropdowns result.
