lookup a list based on a cell and return a choice of values from the list

[Cathmac801]

Hi Folks, I am working on a spreadsheet that has 2 tabs "Master sheet" "Rule type"

On the Rule Type sheet there is a column called 'Location' (this is a number and the same number can show up multiple times), there is also a column called 'Rule Type' - for each location there are multiple rule types (this sheet has c1500 rows)

On the Master sheet there are multiple columns, one is called 'Location' and another is called 'rule type' - i want to set up the 'rule type' cell so that when you click on it only the rule type options for the specific location are available (i.e. a drop down list).

I have seen some examples on my research but can't seem to find exactly what i'm looking for, any help much appreciated.

This is an example of the 'Rule Type' sheet

Location	Rule Type
1	XRJ-111
1	RZ2-156
1	3% annually
7	XTP-876
7	3% annually
954	WEQ-456

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of both sheets, "Master sheet" and "Rule type" sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Cathmac801]

Hi Folks, I am working on a spreadsheet that has 2 tabs "Master sheet" "Rule type"

On the Rule Type sheet there is a column called 'Location' (this is a number and the same number can show up multiple times), there is also a column called 'Rule Type' - for each location there are multiple rule types (this sheet has c1500 rows)

On the Master sheet there are multiple columns, one is called 'Location' and another is called 'rule type' - i want to set up the 'rule type' cell so that when you click on it only the rule type options for the specific location are available (i.e. a drop down list).

I have seen some examples on my research but can't seem to find exactly what i'm looking for, any help much appreciated.

This is an example of the 'Rule Type' sheet

Location	Rule Type
1	XRJ-111
1	RZ2-156
1	3% annually
7	XTP-876
7	3% annually
954	WEQ-456

MrExcelQ300721.xlsx
	A	B	C
1	Name	Location	Rule Type
2	Joe Bloggs	9
3	Jack Sprat	11
4	Pauline Murphy	33
5	Catherine Enright	31
Master Sheet

MrExcelQ300721.xlsx
	A	B
1	Location	Rule Type
2	9	XT39Y-9
3	9	XT23-9
4	9	IBS100-9
5	9	XT42E-9
6	9	XT31X12-9
7	9	XT26X10-9
8	9	XT45E-9
9	9	XT29E-9
10	9	XT43E-9
11	9	XT9-9
12	11	XT26X4-11
13	11	BE-11
14	11	XT26-11
15	11	XT26X4Y-11
16	11	IBS100-11
17	11	XT52-11
18	31	IBS100-31
19	31	XT6-31
20	31	BE-31
21	31	XD98M7-31
22	31	XT9-31
23	31	XD98M11-31
24	31	XD98M9-31
25	31	XD98M5-31
26	31	XD98M1-31
27	31	XD98M10-31
28	33	BE-33
29	33	XD97-33
30	33	XD95M1-33
31	33	XT6B-33
32	33	XT6-33
33	33	XD103M1-33
34	33	XT6BY-33
35	33	XT6BX6-33
36	33	XT6X3-33
37	33	XT6Z7-33
38	33	XT6BX9-33
39	33	XT6X7-33
40	33	IBS100-33
41	33	XT6X6-33
42	33	XT6X1-33
43	33	XT6BX1-33
44	33	XT6XZ12-33
45	33	XT6X5-33
46	33	XT6Z5-33
47	33	XT6X11-33
48	33	XT6X12-33
49	33	XT6X8-33
50	33	XT6X4-33
51	33	XT6BX10-33
Rule Type

MrExcelQ300721.xlsx
	A	B
18	31	IBS100-31
19	31	XT6-31
20	31	BE-31
21	31	XD98M7-31
22	31	XT9-31
23	31	XD98M11-31
24	31	XD98M9-31
25	31	XD98M5-31
26	31	XD98M1-31
27	31	XD98M10-31
28	33	BE-33
29	33	XD97-33
30	33	XD95M1-33
31	33	XT6B-33
32	33	XT6-33
33	33	XD103M1-33
34	33	XT6BY-33
35	33	XT6BX6-33
36	33	XT6X3-33
37	33	XT6Z7-33
38	33	XT6BX9-33
39	33	XT6X7-33
40	33	IBS100-33
41	33	XT6X6-33
42	33	XT6X1-33
43	33	XT6BX1-33
44	33	XT6XZ12-33
Rule Type

What i would like to do is:
On Master sheet in cell C2 - i want the formula to look at cell B2 and then go to the 'rule type' sheet and find what is in B2 from column A and give me a list in C2 on the master of what is in column B on the rule type sheet.
So in this example cell C2 would have a list that would read (below) and the user would be able to select the rule they wanted.

XT39Y-9

XT23-9

IBS100-9

XT42E-9

XT31X12-9

XT26X10-9

XT45E-9

XT29E-9

XT43E-9

XT9-9

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Master sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Double click on any cell in column C.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge < 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, rng As Range, val As String
    Set srcWS = Sheets("Rule Type")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS
        .Cells(1).CurrentRegion.AutoFilter 1, Target.Offset(, -1).Value
        For Each rng In srcWS.Range("B2", srcWS.Range("B" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
            If val = "" Then val = rng Else val = val & "," & rng
        Next rng
    End With
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Application.Transpose(val)
    End With
    val = ""
    srcWS.Cells(1).AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Cathmac801]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Master sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Double click on any cell in column C.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge < 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, rng As Range, val As String
    Set srcWS = Sheets("Rule Type")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS
        .Cells(1).CurrentRegion.AutoFilter 1, Target.Offset(, -1).Value
        For Each rng In srcWS.Range("B2", srcWS.Range("B" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
            If val = "" Then val = rng Else val = val & "," & rng
        Next rng
    End With
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Application.Transpose(val)
    End With
    val = ""
    srcWS.Cells(1).AutoFilter
    Application.ScreenUpdating = True
End Sub

@mumps, sincere apologies for not getting back to you on this sooner. my developer was broken and i was waiting on a new laptop. I don't know what to say, this is absolute genius. Thank you so much, it has saved me so much time and looks to be exactly what i need. very much appreciated.

 

[Cathmac801]

@mumps, sincere apologies for not getting back to you on this sooner. my developer was broken and i was waiting on a new laptop. I don't know what to say, this is absolute genius. Thank you so much, it has saved me so much time and looks to be exactly what i need. very much appreciated.

@mumps - sorry i am very new to vba - i had to move the location of my data around on my master sheet so now 'location' has moved starting in cell H4 (previously B2) and the 'rule type' where i need the drop down is in colum O - starting in O4 (previously C2)
I tried to change the code to H4 and column H but its not working for me now.
Would you be able to point out where i am going wrong please?

Thanks for your help

 

[mumps]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge < 1 Then Exit Sub
    If Target.Column <> 15 Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, rng As Range, val As String
    Set srcWS = Sheets("Rule Type")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS
        .Cells(1).CurrentRegion.AutoFilter 1, Target.Offset(, -7).Value
        For Each rng In .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
            If val = "" Then val = rng Else val = val & "," & rng
        Next rng
    End With
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Application.Transpose(val)
    End With
    val = ""
    srcWS.Cells(1).AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Cathmac801]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge < 1 Then Exit Sub
    If Target.Column <> 15 Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, rng As Range, val As String
    Set srcWS = Sheets("Rule Type")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS
        .Cells(1).CurrentRegion.AutoFilter 1, Target.Offset(, -7).Value
        For Each rng In .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
            If val = "" Then val = rng Else val = val & "," & rng
        Next rng
    End With
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Application.Transpose(val)
    End With
    val = ""
    srcWS.Cells(1).AutoFilter
    Application.ScreenUpdating = True
End Sub

thanks @mumps very helpful

 

[mumps]

You are very welcome.