Filter / Limit list by value chosen in another list?

[mdavidge]

Hello,

I have a list of territory numbers (A Column) and a list of customers (B Column). I would to create a lookup list where I can choose a specific territory number like 650 below. I would like to populate D Colum with only customers begining with "650". I would like this D Column list to change based on the chosen territory number from the drop list for territory.

Is this possible? I am racking my brain and unable to find a solution.

Thanks,
Mike

Book1
	A	B	C	D
1	TERR	ASCEND CUST	TERR SELECTED	LIST C1 CUSTOMERS ONLY
2	610	610 Acustomer	650
3	620	610 Bcustomer
4	640	610 Ccustomer
5	650	620 Acustomer
6	660	620 Dcustomer
7	670	620 Mcustomer
8	680	640 Ccustomer
9	690	640 Ecustomer
10		640 Hcustomer
11		650 Fcustomer
12		650 Jcustomer
13		650 Xcustomer
14		660 Ccustomer
15		660 Mcustomer
16		670 Kcustomer
17		670 Ocustomer
18		680 Bcustomer
19		680 Qcustomer
20		690 Ecustomer
21		690 Fcustomer
22		690 Ycustomer
Sheet3

 

[Norie]

Mike

Are you trying to create cascading validation lists?

i.e. the validation list in D2 is based on the value selected from the validation list in C2

 

[mdavidge]

All,

Tom received the file by e-mail recomended the following. Now the list works as intended. When a particular territory number is chosen in C2, only items from B with that territory number are listed in D.

Thanks to everyone for the help.

Mike

Tom Urtis Wrote

Do this:



From your worksheet, press Alt+F11.

Press Ctrl+G.



Enter

Application.EnableEvents = True

And press Enter



Pres Alt+Q to return to the worksheet.



Try the drop-down again in cell C2.

 

[mdavidge]

Norie,

That is exactly correct.

Mike

 

[mdavidge]

The condsolidated solition to the problem from Tom Urtis is below.

If you are not using Excel97, then see if this helps. Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$2" Then Exit Sub
Application.ScreenUpdating = False
If IsEmpty(Target) Then
Range("D2:D" & Cells.Rows.Count).Clear
Exit Sub
End If
ActiveSheet.AutoFilterMode = False
Range("D2:D" & Cells.Rows.Count).Clear
Dim FilterRange As Range, LR&, FilterWhat$
LR = Cells(Rows.Count, 2).End(xlUp).Row
Set FilterRange = Range("B1:B" & LR)
FilterWhat = Target.Value
FilterRange.AutoFilter Field:=1, Criteria1:=FilterWhat & "*"
On Error Resume Next
With FilterRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy Range("D2")
End With
Err.Clear
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
_________________

Close the module and then from your worksheet, press Alt+F11.
Press Ctrl+G.

Enter
Application.EnableEvents = True
And press Enter

Pres Alt+Q to return to the worksheet.


Tom Urtis