Excel Dropbox Help

Hi all,

I'm hoping someone can help me out with a dropbox problem.

I am using two dropboxes on each row of data. The following code is attached to the first dropbox -

Sub DropDown10_Change()

If Cells(2, 5) = 1 Then
ActiveSheet.Shapes("Drop Down 1").Select
With Selection
.ListFillRange = "Inputs!$A$2:$A$9"

End With
End If

If Cells(2, 5) = 2 Then
ActiveSheet.Shapes("Drop Down 1").Select
With Selection
.ListFillRange = "Inputs!$B$2:$B$9"

End With
End If

If Cells(2, 5) = 3 Then
ActiveSheet.Shapes("Drop Down 1").Select
With Selection
.ListFillRange = "Inputs!$C$2:$C$5"

End With
End If

End Sub

The dropbox is linked to the cell it sits in. So the code basically reads the number in the cell, and depending on what option was selected - decides what to populate the second drop box with.

The problem has stemmed from my need to copy these drop boxes down (up to 1000 times). Is there some way to -

a) Automatically update the cell link in the format control of the drop box as I fill the box down (ie. update from A1 to A2 when dragged down)

b) Instead of using "Cells(2,5)", use a bit of code to say - 'whatever cell the drop box is linked to'.

c)Overcome the naming problem with filling the boxes down. ie. box in A1 and A2 are 'Dropbox 10', so instead of referring to them by name, refer to them as 'selected drop box'.

Hope this makes sense. I don't think I can do what I want to do (without programming the macro and renaming each drop box). I think the only solution is for me to have one data entry sheet, and another for storing the info. - this seems more practical - however is not suitable as I intend this to be a data entry page with lots of repetitive info. The feature of excel that picks up on data already entered (ie if you have john in a cell and type 'j' john will come up automatically) is important. This would be lost with a seperate input page.

Any advice would be appreciated!

Cheers

systematic