Filter / Limit list by value chosen in another list?

mdavidge

Board Regular
Joined
Oct 14, 2005
Messages
87
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
ABCD
1TERRASCEND CUSTTERR SELECTEDLIST C1 CUSTOMERS ONLY
2610610 Acustomer650
3620610 Bcustomer
4640610 Ccustomer
5650620 Acustomer
6660620 Dcustomer
7670620 Mcustomer
8680640 Ccustomer
9690640 Ecustomer
10640 Hcustomer
11650 Fcustomer
12650 Jcustomer
13650 Xcustomer
14660 Ccustomer
15660 Mcustomer
16670 Kcustomer
17670 Ocustomer
18680 Bcustomer
19680 Qcustomer
20690 Ecustomer
21690 Fcustomer
22690 Ycustomer
Sheet3
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
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

Board Regular
Joined
Oct 14, 2005
Messages
87
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Hi madavidge:

Following is an AdvancedFilter solution ...
Book1
ABCDEF
1TERRASCEND_CUSTTERRTERRASCEND_CUST
2610610_Acustomer610650620_Acustomer
3620610_Bcustomer620Fcustomer
4640610_Ccustomer640Jcustomer
5650620_Acustomer650Xcustomer
6660620_Dcustomer660
7670620_Mcustomer670
8680640_Ccustomer680
9690640-Ecustomer690
10640Hcustomer
11650Fcustomer
12650Jcustomer
13650Xcustomer
14660Ccustomer
15660Mcustomer
16670Kcustomer
17670Ocustomer
18680Bcustomer
19680Qcustomer
20690Ecustomer
21690Fcustomer
22690Ycustomer
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
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 ...
Code:
Private Sub ComboBox1_Change()
    yFilter
End Sub
 

mdavidge

Board Regular
Joined
Oct 14, 2005
Messages
87
Tom,

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

Thanks
Mike
 

mdavidge

Board Regular
Joined
Oct 14, 2005
Messages
87

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
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

Legend
Joined
Sep 3, 2002
Messages
10,473
Have you perhaps run some other code which may have ended by error, leaving enable events turned off?
 

mdavidge

Board Regular
Joined
Oct 14, 2005
Messages
87
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,675
Members
412,481
Latest member
nhantam
Top