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

 

[Tom Urtis]

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

 

[mdavidge]

Tom,

I pasted this as requested. It does not seem to work. However, I see references to AutoFilter in the code. The 650 reference is derived from Validation > List option linking to the A Column. I do not have an AutoFiler option in the D Column either.

Could this be the reason for the issue?

Mike

 

[Tom Urtis]

I know the code works, so a few things:

- It assumes the entry of 650 or whatever is being made in cell C2.
- If this is by data validation in cell C2, the code will not work if you are using Excel 97.
- It assumes the text values in column B really do begin with the entry being made in cell C2.
- Are you sure you placed the code in the sheet module of the same sheet holding the data.

Assumptions were based on my interpretation of your screen shot, and again, regarding Excel97 you'd need to check for that on your system. The code will work if you manually enter the customer number in C2, just not by data validation, if 97 is being used. After 97, the code would work regardless.

See what elese is going on there, do the values in column B actually start with a preceding space or maybe you will spot something else.

 

[Yogi Anand]

Hi madavidge:

Following is an AdvancedFilter solution ...

Book1
	A	B	C	D	E	F
1	TERR	ASCEND_CUST		TERR	TERR	ASCEND_CUST
2	610	610_Acustomer		610	650	620_Acustomer
3	620	610_Bcustomer		620		Fcustomer
4	640	610_Ccustomer		640		Jcustomer
5	650	620_Acustomer		650		Xcustomer
6	660	620_Dcustomer		660
7	670	620_Mcustomer		670
8	680	640_Ccustomer		680
9	690	640-Ecustomer		690
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
23
Sheet10

in cells D1:D9, I created a Unique TERR list

Then below cell E2, I created an ActiveX ComboBox with FD1:D9 as Fill_range, and E2 as Linked_Cell

Then I associated the following code

Sub yFilter()

'12/03/2005 by Yogi Anand
'
    Range("a1").Select
    With Sheets("Sheet10").Range("a1:b22")
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "E1:E2"), CopyToRange:=Range("F1"), Unique:=False
    End With
End Sub

with ComboBox Change event ...

Private Sub ComboBox1_Change()
    yFilter
End Sub

 

[mdavidge]

Tom,

I am running Excel 2002. I will work with your reply a bit here.

Thanks
Mike

 

[mdavidge]

Tom,

- It assumes the entry of 650 or whatever is being made in cell C2.

****The entry is in C2

- If this is by data validation in cell C2, the code will not work if you are using Excel 97.

*****I am running Excel 2002

- It assumes the text values in column B really do begin with the entry being made in cell C2.

*****The file I am working with is exactly as I pasted with the B values

- Are you sure you placed the code in the sheet module of the same sheet holding the data.

******I have double checked and I am working in Sheet 3. The code is definatly in Sheet 3 from right click Sheet 3 > view code.


I have nothing for validation in D Column - is that needed?

Mike

 

[Tom Urtis]

Since column D is the destination column, nothing is needed there. It sounds as if you did everything correctly, so now my curiosity is peaked when every now and then this sort of thing happens.

Do one more check, click on Tools > Macros > Security and on the Security Level tab make sure your security level is not higher than Medium. If it is, set it to medium, save the workbook, close it, close Excel, reopen Excel, reopen the workbook, and try it again (press "Enable Macros" when prompted).

If that still is not the answer, then if you like, email me a copy of the workbook so I can take a look.

 

[just_jon]

Have you perhaps run some other code which may have ended by error, leaving enable events turned off?

 

[mdavidge]

Tom,

I really appreciate the time. Security has been set to Low all along. I will e-mail you the file. I am sorry to impose.

Mike