Dependent drop down list for large worksheet?

[aherzog]

I have a large worksheet with four columns (A, B, C, D) with the titles: Item #, MFG, Model, Qty. There are about a 1000 items in total, which means each of these columns has a 1000 cells each. My goal is to create a second worksheet which is blank but has the same titles. I want a drop down list for each category which are dependent on each other, though only the first three columns will be dependent on each other. For example, the first row says this: Item # (Column A) = 12 (Row 1) ; MFG (Column B) = 43 ; Model (Column C) = Air Duster.

If I choose Item # 12, then I automatically want the MFG - 43 and the Model - Air Duster to show up in their respective columns (kind of like a conditional formatting type situation). Subsequently, if I choose MFG - 43 in Column B, then I automatically want Item # 12 and Model - Air Duster to show up in that same row under their assigned columns.

Is this possible? This is a large spreadsheet with about 1000 items per column. How long would something like this take? And, if what I am looking to accomplish isn't possible, does anyone have any other solutions which may give me similar results? Thanks.

 

[aherzog]

I looked at the updated file, it looks good. How can I get Row 3 to do the same thing as Row 2?

Also, if it has nothing to do with dependent drop down lists, what should it be referred to as? Thanks.

 

[aherzog]

I took a second look, and there is a problem with the updated file. I need the next choice to only be the following column in relation to the table. For example, go to sheet1 - if I choose PXT101NB3S1, there should only be one Item # available as an option: 5466, and also only one Model # available: 10FT STANDARD LAPTOP POWER CORD.

The first code posted in this discussion was the closest solution I had found, and even still I need it to apply to more than only one row of options.

 

[MickG]

If you address the person you are referring to by name, you may get a better response and not need to start a new thread .!!!!.
Try the amended code below (placing it in sheet2):-
Double click "A1" of sheet2 to load Validation lists in Column "A"

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range
Dim Dn As Range
Dim Dic As Object
Dim n As Long
If Target.Address(0, 0) = "A1" Then
With Sheets("Sheet1")
Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
End With
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each Dn In Rng: Dic(Dn.Value) = Empty: Next
For n = 1 To 10 'Alter number of Validation rows you want, here!!!!
With Range("A1").Offset(n).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
End With
Next n
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dn              As Range
Dim Rng             As Range
Dim Dic             As Object
Dim n               As Integer
If Not Intersect(Target, Range("A:D")) Is Nothing Then
  Application.EnableEvents = False
   For n = Target.Column To 4
        Target.Offset(, n).Validation.Delete
        Target.Offset(, n).Value = ""
   Next n
Application.EnableEvents = True
With Sheets("Sheet1")
Set Rng = .Range(.Cells(2, Target.Column), .Cells(Rows.Count, Target.Column).End(xlUp))
End With
  Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   If Not Rng(1).Offset(, 1) = "" Then
   For Each Dn In Rng
       If Dn.Value = Target.Value Then
         If Not Dic.exists(Dn.Offset(, 1).Value) Then
                 Dic(Dn.Offset(, 1).Value) = Empty
            End If
        End If
      Next Dn
   
If Dic.Count > 0 Then
 With Target.Offset(, 1).Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
End With
End If
End If
End If
End Sub

 

[aherzog]

I apologize MickG, I am new to the forums. Thank you for the new code, it works and gives me the results I was looking for.

When I save the file as a macro-enabled file, every time I close it and then open it again I get a message that says: Excel found unreadable content. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

How can I avoid this from happening every time I open the file?

 

[MickG]

I thought I had replied to you, but can't find my post.
I also have the same problem , I don't think Validation likes your list, not sure why. it does not like its length or the fact it has numbers in it.
I'll have another look at it !!!. In the mean time I've change the "Validation drop downs" for "Forms, Drop Down Boxes" that I have now managed to get working, is that something you might be able to use.

 

[aherzog]

I noticed also that the code happened to reference sheet2 rather than sheet1, would that have anything to do with this error message? And what is the difference between "Validation drop downs" and "Forms, Drop Down Boxes"? Is the second one with the insert function?

 

[MickG]

It's to do with the way that the each string is loaded to the validation list.
The difference between Validation and Drop Downs is the Validation list sits in the cell as a string to select from, and a Drop own box is a box you can select from "Developer" ,"Insert" (controls)(Have a try). You place these boxes on the sheet and load data to them in much the same way.

 

[aherzog]

Is there any way to correct the reason for why the error message pops up every time I open the document?

Also, I tried to adjust the amount of rows where you added the side note to the code, though it didn't work =/ - Suggestions?

Thanks for all your help thus far! Regardless, it has been helpful already =)

 

[MickG]

Rethinking your original request , would this be more useful.!!! Just Open the Userform on sheet 2 , the rest is pretty self explanatory
https://app.box.com/s/lfhd4909aq7puabi35w4kzfezdh7gkvs

 

[aherzog]

Do you mind explaining this link? I'm rather confused - I don't understand the two yellow rows as well as the last page. Is this file interactive?