MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

Juan Pablo

Sub Lists()
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
End If
Next Nm

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
End If
End If
Next i
End Sub

Posted by Aladin Akyurek on November 20, 2001 7:14 AM

Thanks, Juan. I or Maureen might have additional conditions. So be prepared.



Posted by Juan Pablo on November 20, 2001 7:27 AM

I would say then.... prepare your check book !!! :)

P.D. Wire transfers, Credit card payments, are also accepted.... :)

Juan Pablo