Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Lookup
I have an Excel workbook with various sheets!
(1) sheet (Recipe Input) has a form named frmRecipeCostingData with a Combobox named (Ingredient) Textbox named (Unit) Textbox named (Unit Cost) Another sheet named (Food Inventory) has columns A (Ingredient), G (Unit), & I (Unit Cost) I want to use the combobox in the form to lookup the ingredient on Food Inventory Sheet and pull the corresponding data in column G & I into the form. Where do I start? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Lookup
On Sep 5, 2:15 pm, "Dudy" wrote:
I have an Excel workbook with various sheets! (1) sheet (Recipe Input) has a form named frmRecipeCostingData with a Combobox named (Ingredient) Textbox named (Unit) Textbox named (Unit Cost) Another sheet named (Food Inventory) has columns A (Ingredient), G (Unit), & I (Unit Cost) I want to use the combobox in the form to lookup the ingredient on Food Inventory Sheet and pull the corresponding data in column G & I into the form. Where do I start? Hi, I did something similar a while back - this might get you started. The code below populates a combo box from data in a spreadsheet. When you said that one sheet has a form, etc.. did you mean that there is a trigger (button) on that sheet to open that form? I've subbed your sheet name in the first bit of code below - basically in the Private Sub cboCourse_Change() section, you can specify what will happen when that combo box (Ingredient) is changed. Hope this is helpful, Louis ---------------------- private sub UserForm_Initialize() Dim MyUniqueList '////combo box setup 'Set up primary (first) combo box (cboCourse) With Me.cboCourse '.Clear ' clear the listbox content MyUniqueList = UniqueItemList1(Range("Food Inventory!A1:A300"), True) For i = 1 To UBound(MyUniqueList) .AddItem MyUniqueList(i) Next i = 0 .ListIndex = 0 ' select the first item End With '////Background code for combo box population '////No Editing Private Function UniqueItemList1(InputRange1 As Range, _ HorizontalList1 As Boolean) As Variant Dim cl_1 As Range, cUnique1 As New Collection, i As Long, uList1() As Variant Application.Volatile On Error Resume Next '////first collection (cUnique) for including blank spaces and '////maintaining index count for spreadsheet :) For Each cl_1 In InputRange1 'If cl.Formula < "" Then cUnique1.Add cl_1.Value, CStr(cl_1.Value) 'End If Next cl_1 '////set up count for spreadsheet UniqueItemList1 = "" If cUnique1.Count 0 Then ReDim uList1(1 To cUnique1.Count) For i = 1 To cUnique1.Count uList1(i) = cUnique1(i) Next i UniqueItemList1 = uList1 If Not HorizontalList1 Then UniqueItemList1 = _ Application.WorksheetFunction.Transpose(UniqueItem List1) End If End If On Error GoTo 0 End Function Private Sub cboCourse_Change() 'Check if ListIndex = 0 (first option in combo box) With Me.cboCourse2 '.Clear If Me.cboCourse.ListIndex = 0 Then Label4.Visible = False Me.cboCourse2.Visible = False ..Clear ' Clear the list box content 'MyUniqueList3 = UniqueItemList2(Sheet1.Range("B8:B12"), True) 'For i = 1 To UBound(MyUniqueList3) '.AddItem MyUniqueList3(i) 'Next i 'Me.cboCourse2.ListIndex = 0 End If End With 'Check if ListIndex = 1 or "all" (second option in combo box) With Me.cboCourse2 '.Clear If Me.cboCourse.ListIndex = 1 Then Me.Label4.Visible = True Me.cboCourse2.Visible = True ..Clear ' Clear the list box content '////Edit range below 'the variable below is to populate the combo box MyUniqueList2 = UniqueItemList2(Sheet1.Range("B8:B400"), True) '////End Edit 'the variable below will have spaces in the listing - for 'an accurate index count of row position 'MyUniqueList2a = UniqueItemList1(Sheet1.Range("B8:B385"), True) 'loop below for combo box For i = 1 To UBound(MyUniqueList2) ..AddItem MyUniqueList2(i) Next i 'i = 0 Me.cboCourse2.ListIndex = 0 'select the first item End If End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) |