Code for Aladin - Dropdown list dependent on another ...Here is the code.
Posted by Juan Pablo on November 20, 2001 6:22 AM
Aladin, try this macro. Basically what it does is, first, erase them all, and starts looking in EACH cell of the MLIST range. It looks for "Phase x", where x is the counter of each cell (Project Name 1 is 1, Project Name 2 is 2, etc., not saying that i extract the last part of the cell to get the number...) in the sheet, and assigns a new named range like this: If cell found was B1, the range goes from B2 to last cell with data on that column (Control + Down).
This can be assigned to a button, BUT, i'm assuming it has to be run from the "List" sheet, where all the Sublists are.
Workbook is underway to you.
Dim MList As Range
Dim Cll As Range
Dim Nm As Name
Dim WSD As Worksheet
Dim Str As String
Dim i As Integer
Set WSD = ActiveSheet
Set MList = Range("MLIST")
For Each Nm In ThisWorkbook.Names
If Right(Left(Nm.RefersTo, InStr(1, Nm.RefersTo, "!", 1) - 1), Len(Left(Nm.RefersTo, InStr(1, Nm.RefersTo, "!", 1) - 1)) - 1) = WSD.Name Then
If Nm.Name <> "MLIST" Then Nm.Delete
For i = 1 To MList.Cells.Count
Str = Application.Substitute(MList(i), " ", "")
Set Cll = WSD.Cells.Find("Phase " & i, LookAt:=xlWhole)
If Not Cll Is Nothing Then
If Intersect(MList, Cll) Is Nothing Then
Names.Add Name:=Str, RefersTo:="='" & WSD.Name & "'!" & Range(Cll.Offset(1), Cll.End(xlDown)).Address