Dependent Data Validation with Large List

[IainRourke11]

Hi all,

I have a bunch of information copied from the internet in a nice structured table. I am trying to create a separate table that a user can make selections using data validation drop downs. These drop downs will be dependent on what the user has selected previously.

I have done something similar before, by making unique lists, then naming them such that when the user uses the 2nd drop down list, only the appropriate information is available, based on their choice in the first drop down. There are a few really good examples of this on youtube.


My issue is, that the table is very long, so it would take me a while to set up all the necessary unique lists such that my multiple data validation drop downs would function correctly. Is there a quicker way I can go about creating and naming my lists. Thats the only time consuming bit for me, once that is done, I am good. Below is an small extract of the table I am working with:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]OD[/TD]
[TD]Connection[/TD]
[TD]Grade[/TD]
[TD]Adjusted Weight[/TD]
[/TR]
[TR]
[TD]Drill Pipe
[/TD]
[TD]5"
[/TD]
[TD]NC50[/TD]
[TD]S-135[/TD]
[TD]24.11[/TD]
[/TR]
[TR]
[TD]Drill Pipe[/TD]
[TD]5"[/TD]
[TD]NC50[/TD]
[TD]G-105[/TD]
[TD]24.11[/TD]
[/TR]
[TR]
[TD]Drill Pipe[/TD]
[TD]5"[/TD]
[TD]XT50[/TD]
[TD]S-135[/TD]
[TD]23.65[/TD]
[/TR]
[TR]
[TD]Drill Pipe[/TD]
[TD]5 1/2"[/TD]
[TD]TT550[/TD]
[TD]TSS-105[/TD]
[TD]25.3[/TD]
[/TR]
[TR]
[TD]Drill Pipe[/TD]
[TD]5 1/2"[/TD]
[TD]XT54[/TD]
[TD]HS3-125[/TD]
[TD]28.5[/TD]
[/TR]
[TR]
[TD]HWDP
[/TD]
[TD]3 1/2"[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]HWDP[/TD]
[TD]3 1/2"[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]HWDP[/TD]
[TD]3 1/2"[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]HWDP[/TD]
[TD]3 1/2"[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]HWDP[/TD]
[TD]5"[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]HWDP[/TD]
[TD]5"[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]HWDP[/TD]
[TD]5"[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]HWDP[/TD]
[TD]5"[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Drill Collars[/TD]
[TD]6 1/2"[/TD]
[TD]NC46[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Drill Collars[/TD]
[TD]6 1/2"[/TD]
[TD]NC50[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Drill Collars[/TD]
[TD]8"[/TD]
[TD]6 5/8" Reg[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Drill Collars[/TD]
[TD]8 1/4"[/TD]
[TD]6 5/8" Reg[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Wash Pipe
[/TD]
[TD]2 7/8"[/TD]
[TD]PH-6[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Wash Pipe[/TD]
[TD]5"[/TD]
[TD]Hydril CS[/TD]
[TD]AC-95[/TD]
[TD]etc.[/TD]
[/TR]
</tbody>[/TABLE]

So the user will use 5 drop downs (column headings in row 1) on a separate sheet, based on their selections, a look up function will display some property in column 6 (not shown above). I know how to do the lookup/match/index part of this too. Really just trying to simplify the workload of getting unique lists created.

First they will choose between Drill Pipe, HWDP, Drill Collars and Wash Pipe. That's simple enough. So if I chose drill pipe in the first column, when I choose from the second column, I only want 5" and 5 1/2" to appear, like wise for the third column and so on.

Any help would be appreciated, let me know if you need more info.

Thanks,

Iain.

 

[MARK858]

Is there a quicker way I can go about creating and naming my lists.

If your data starts in A1 try running the code below to create your named ranges (names will be as per row1).

Sub AddNames()
    Dim Rws As Long, Cols As Long, Rng As Range
    
    Rws = Cells(Rows.Count, "A").End(xlUp).Row
    Cols = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Set Rng = Range(Cells(1, 1), Cells(Rws, Cols))
    Rng.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
                    False
End Sub

 

[IainRourke11]

Thanks, that almost worked for my data set, however the named lists that were created had duplicates.

Also, the user would be able to select something in say column 3, that doesn't exist. To use a different example, it would be like saying:

Fruit/Veg: Fruit Type of Fruit: Banana Coulor of fruit: Blue

I would like to create my drop downs such that the user cant select an incorrect colour, so in the corrected version, only yellow would be available to the user.

Thanks for your help.

Iain.

 

[MARK858]

Creating the dependent dropdowns I didn't state the code did and am still not that is down to you as in

Any help would be appreciated,

As for the unique items the code below will create the unique items and create named ranges in a new sheet.
It will not create your dependent dropdowns.

Change Sheet2 to your original sheets name.

Sub AddNames()
    Dim Rws As Long, Cols As Long, Rng As Range, xCol As Long

    With Sheets("Sheet2")
        Rws = .Cells(Rows.Count, "A").End(xlUp).Row
        Cols = .Cells(1, Columns.Count).End(xlToLeft).Column
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "mySht"

        For xCol = 1 To Cols

            .Range(.Cells(1, xCol), .Cells(Rws, xCol)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("mySht").Cells(1, xCol), Unique:=True
        Next
    End With
    Set Rng = Sheets("mySht").Range(Sheets("mySht").Cells(1, 1), Sheets("mySht").Cells(Rws, Cols))
    Rng.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
                    False
End Sub

 

[IainRourke11]

Thanks for your help Mark858.

Iain.