Dynamic Dropdown list During Data Entry

[Arishy]

I have a table Consists of part numbers and prices in two column List.
I have a template that lists all possible items to order.
Each list ( Price list and order list are on seperate sheets)

The order list consists of Item description/Quantity to order/price

When I punch the quantity in this list The VBA code "Knows" which part
of the price list to go to BUT This specific part can be supplied by
Say 3 suppliers, ALL have a unique suffix

Let me expand

The part# consists of two parts XX supplier Code, YY part #

The parts that can be supplied by several suppliers must use item
range XX80 XX99 Also these parts have same number for say the three
supplier So if we have 3 supplier providing the SAME part # (with
different prices of course);

this part is coded 1184/4484/5084 The item number is the same (84)
But from different supplier Three Supplier 11, 44, and 50

Where is the Problem ?

I need to get to CHOOSE ONE of these suppliers after I punch the
quantity to get the correct price based on MY CHOICE OF A SUPPLER

Suppose I use Combobox I need to pick the supplier from this list.
But since it is a dynamic price list the Template list should reflect
the current "suppliers" for that item.

In other words if a new supplier for part # 84 with code 9984
I get the name of the supplier(99) added to the combobox. This is, I am
sure, beyond me. Of course If I push myluck here and venture not only
supplier name but a price so I can choose the cheapest

But this is an option.

(supplier number or name can be solved by a simple lookup table I hope)




PS The combobox is merely a suggestion

Sorry for the "wordy" email I am counting on your patience

 

[Tom Ogilvy]

You say the item description is in the Order list, but the information in
the price is part number. How do you translate item description to part
number or are item description and part number on and the same.

 

[samir arishy]

Very good question ...Boy you are smart

When I design the template The item description in say A5
and I know it is provided by one supplier only because I put there in a
hidden column next to it the full part #
say AAAAA is item number 9501

I am doing this to make it easy for the user not to use part # at all.

NOW the fun part

For those duplicate unique numbers I only put the item #
without the supplier # so I will put there 85 in the hidden column

I will make sure that any 2 digit code are in the range 80-99

When the user puts a quantity I should get the drop down list ( 85 will
select ALL items that have suffix of 85 in it

The result is the supplier drop list (the first two digits of the
4-digit # this list is all suppliers that can provide that item

Hope I am clear enough

 

[Tom Ogilvy]

You can react to the users entry of quantity by using the change event.
Right click on the sheet tab where the entry is made and select view code.

in the resulting module, you can put in code like this:

Assume quantity is entered in C5

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if Target.count > 1 then exit sub
if Target.Address = "$C$5" then
With Worksheets("Price List")
set rng = .Range(.Cells(2,1),.Cells(row.count,1).End(xlup))
End With
Combobox1.Clear
Combobox1.ListCount = 2
for each cell in rng
if Right(cell,2)=Range("B5").Value
combobox1.AddItem cell.value
combobox1.List(.combobox1.Listcount-1,1) = _
cell.offset(0,1)
end if
Next
End if
End Sub

This is assuming a combobox from the control toolbox toolbar is named
Combobox1 and will hold the list.

Chip Pearson's page on events
http://www.cpearson.com/excel/events.htm