Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Dropdown list During Data Entry
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Dropdown list During Data Entry
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. -- Regards, Tom Ogilvy "Arishy" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Dropdown list During Data Entry
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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Dropdown list During Data Entry
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 -- Regards, Tom Ogilvy "samir arishy" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic DropDown list from Unsorted vector | Excel Worksheet Functions | |||
Need to have a dropdown list or allow other entry? | Excel Discussion (Misc queries) | |||
Data Validation - Restrict entry but without dropdown | Excel Discussion (Misc queries) | |||
applying dropdown list entry to multple cells | New Users to Excel | |||
Preventing manual entry in dropdown list ... possible? | Excel Discussion (Misc queries) |