i had the same problem a while ago and found that no one could help me
unfortunately so i tried a few things and came up with this (i am sure
there is a easier way however does the job). i attached a excel
document but incase you do not want to open it i will explain

List 1 list 2 list 3 list4 list
5 list 6 list 7
company company 1 fact 1 emplo 1 boss
1 etc
factory company 2 fact 2 emplo 2
boss 2 etc
employee company 3 fact 3 emplo 3
boss 3 etc
employer company 4 fact 4 emplo 4
boss 4 etc
etc etc etc etc
etc etc
etc etc etc etc
etc etc

Now i attached list 1 to a drop down box and attached list 2 to another
drop down box and assign a macro to drop down box 1 and the macro looks
like this

Sub DropDown1_Change()
' if the index number of the list = 1 then list = factory
'so copy the factory list
If Range("b2") = 1 Then
Range("G1:G19").Select
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
If Range("b2") = 2 Then
Range("H1:H19").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
If Range("b2") = 3 Then
Range("I1:I19").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
If Range("b2") = 4 Then
Range("J1:J19").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
If Range("b2") = 5 Then
Range("K1:K19").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
If Range("b2") = 6 Then
Range("L1:L19").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
If Range("b2") = 7 Then
Range("M1:M19").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
If Range("b2") = 8 Then
Range("N1:N19").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
If Range("b2") = 9 Then
Range("o1:o19").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End If
' selects the second drop down box to equal 1 so top of list
Range("e2") = 1
'this line makes the copy crop lines go away
Application.CutCopyMode = False
End Sub


Basically what it does is copy the list and pastes it into the list 2
space to provide the second drop down box with values hope this helps


+-------------------------------------------------------------------+
|Filename: Book2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4841 |
+-------------------------------------------------------------------+

--
Zygan
------------------------------------------------------------------------
Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423
View this thread: http://www.excelforum.com/showthread...hreadid=548022