MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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
.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.