Dropdown List Based On Criteria

[animas]

customerdb.xls

[B]Column A           Column B
Customer ID      Suppliers[/B]
0023                 ABC Co.
0046                 XYZ Co.
0023                 qqq Co.
0034                 MNO Co.
0023                 ppp Co.

in another sheet in cell B2 user enters customer code(say 0023).
in cell C2 user is required to enter a single supplier of that customer(0023) from a dropdown list(source customerdb.xls). usually the dropdown list of C2 will show all the suppliers name where i need to show only 0023's suppliers in the dropdown.

so what i need is to filter C2's dropdown list based on customer id entered in cell B2.

how should i do it?

 

[jeffreybrown]

You might want to take a look at dependent lists...

http://www.contextures.com/xlDataVal02.html

 

[animas]

http://www.contextures.com/xlDataVal13.html serves my purpose partially since data needs to be in sorted form.
any other workarounds?

 

[jeffreybrown]

A dependent list is the only way I know how to manipulate a drop down box without maybe using vba, but it would seem to me you would still have to put the values in some type of logical order.

For the vba part I would not know how to do something like this. Maybe we can bump this to the top and somebody else has an idea.

 

[MickG]

Hi,This code (Using the Change Event) retrieves values from the Source Range in Column "B" Sheet (2) to reflect the item selected in the Active sheet ("B2").
These values are then Pasted in the ActivesheetRange ("IV1") down.
The Validation List in the Activesheet "C2" is then automatically updated to reflect these changes.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] ray, c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "B2" [COLOR=navy]Then[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet2")
[COLOR=navy]Set[/COLOR] Rng = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
 ReDim ray(1 To Rng.Count)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Range("B2").Value = Dn.Offset(, -1) [COLOR=navy]Then[/COLOR]
         c = c + 1
        ray(c) = Dn.Value
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
    [COLOR=navy]If[/COLOR] c > 1 [COLOR=navy]Then[/COLOR]
        Range("IV1").Resize(c).Value = Application.Transpose(ray)
        Range("C2").Select
        Range("C2").Validation _
            .Modify xlValidateList, xlValidAlertStop, _
            xlBetween, "=" & Range("IV1").Resize(c).Address & ""
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[animas]

hi Mick, can i have look at the excel file of the code you have pasted. that would be easier fro me to grasp.

my purpose is similar to advanced filtering. say each row has customerID and supllierName. there are multiple suppliers of same customer id. in another sheet there will be a cell B2 where user inputs a specific customer id. And in Cell dropdown list will be updated with the supplier's name of that custID only. now user inputs a supplier name in cell C2 from the updated dropdown list.

 

[MickG]

Hi, Have a look at the example below !!
animas.xls
Regards Mick

 

[animas]

simple n elegant. will be a gr8 reference for others.
that is an informative example. thanks for the extra works, Mick.

if use another file(custDB.xls) for data source instead of Sheet2 where should i make the change?

 

[MickG]

Hi, Change Line
"With Sheets("Sheet2") " To :-
"With Workbooks("custDB.xls").Sheets("Sheet2")"
NB:- Make sure the other workbook is open !!
Regards Mick

 

[animas]

much appreciated.