Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
Hi
i have this in a sheet in 3 collumns A=date -B= number -C= value I want to see the value(C), for date - number Like: if i in 2 tekstboxes write the date in one and the number in the second box then i get the value From C where A=the first tekbox and B = the second tekstbox Best regards Alvin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
You can use this kind of syntax:
=index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) (still an array formula) alvin Kuiper wrote: Hi i have this in a sheet in 3 collumns A=date -B= number -C= value I want to see the value(C), for date - number Like: if i in 2 tekstboxes write the date in one and the number in the second box then i get the value From C where A=the first tekbox and B = the second tekstbox Best regards Alvin -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
Hi dave
thanks I have change the way i want it a little bit I have now i combo box there show det dates there are in collumn a ( i have sort the dates brfore this) Its made so the dates only comes one time in the combobox even the date are there maybe 20 times imn the collumn , the i get an range where all this dates are in Maybe from A1 to A20, from this range i get the numbers in a second combobox -- all this working fine. But when i selct a number in my second combobox i want to see where the value are in in collunm, so i can get the value from C collumn Try to see here all code: Private Sub combo_sedatoer_Click() Dim hvora As String Dim ch As String Dim s As String Dim s1 As String combo_timer.Clear txtvaerdi.Text = "" MsgBox combo_sedatoer.Value hvorb = Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1) s1 = Replace(hvorb, "A", "B") Dim rng3 As Range Sheets("Ark1").Select Set rng3 = Range(Range("Ark1!" & s1), Range("ark1!" & s1)) Me.combo_timer.List = rng3.Value End Sub Private Sub combo_sedatoer_DropButtonClick() Dim i As Long Dim iStart As Long Dim iEnd As Long Dim dtePrev As Date Dim iArray As Long Dim ary dtePrev = 0: iArray = 1 ReDim ary(1 To 2, 1 To 1) With Worksheets("Ark1") Me.combo_sedatoer.Clear For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row If .Cells(i, "A").Value < dtePrev Then If i < 1 Then ReDim Preserve ary(1 To 2, 1 To iArray) ary(1, iArray) = dtePrev ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address iArray = iArray + 1 End If iStart = i iEnd = i dtePrev = .Cells(i, "A").Value Else iEnd = i End If Next i ReDim Preserve ary(1 To 2, 1 To iArray) ary(1, iArray) = dtePrev ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address Me.combo_sedatoer.List = Application.Transpose(ary) End With End Sub So it it the address from Me.combo_timer I want Can't use listindex Reagrds Alvin "Dave Peterson" wrote: You can use this kind of syntax: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) (still an array formula) alvin Kuiper wrote: Hi i have this in a sheet in 3 collumns A=date -B= number -C= value I want to see the value(C), for date - number Like: if i in 2 tekstboxes write the date in one and the number in the second box then i get the value From C where A=the first tekbox and B = the second tekstbox Best regards Alvin -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
Maybe just looping through all the rows looking for a match in the first and
second column would be sufficient. This may give you another idea. But be careful--textboxes return text. So if your values are dates/numerics, you'll have to do something to make the comparisons match. Option Explicit Dim myRng As Range Private Sub ComboBox1_Change() Dim myCell As Range Me.ComboBox2.Clear Me.ComboBox3.Clear If Me.ComboBox1.ListIndex < 0 Then Exit Sub End If For Each myCell In myRng.Columns(1).Cells If myCell.Value = CDate(Me.ComboBox1.Value) Then Me.ComboBox2.AddItem myCell.Offset(0, 1).Value End If Next myCell End Sub Private Sub ComboBox2_Change() Dim myCell As Range Me.ComboBox3.Clear If Me.ComboBox2.ListIndex < 0 Then Exit Sub End If For Each myCell In myRng.Columns(1).Cells If myCell.Value = CDate(Me.ComboBox1.Value) Then If myCell.Offset(0, 1).Value = Me.ComboBox2.Value Then Me.ComboBox3.AddItem myCell.Offset(0, 2).Value End If End If Next myCell End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() Dim myCell As Range Set myRng = Worksheets("sheet1").Range("a1:c20") For Each myCell In myRng.Columns(1).Cells 'do your stuff to make it unique Me.ComboBox1.AddItem Format(myCell.Value, "mm/dd/yyyy") Next myCell End Sub alvin Kuiper wrote: Hi dave thanks I have change the way i want it a little bit I have now i combo box there show det dates there are in collumn a ( i have sort the dates brfore this) Its made so the dates only comes one time in the combobox even the date are there maybe 20 times imn the collumn , the i get an range where all this dates are in Maybe from A1 to A20, from this range i get the numbers in a second combobox -- all this working fine. But when i selct a number in my second combobox i want to see where the value are in in collunm, so i can get the value from C collumn Try to see here all code: Private Sub combo_sedatoer_Click() Dim hvora As String Dim ch As String Dim s As String Dim s1 As String combo_timer.Clear txtvaerdi.Text = "" MsgBox combo_sedatoer.Value hvorb = Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1) s1 = Replace(hvorb, "A", "B") Dim rng3 As Range Sheets("Ark1").Select Set rng3 = Range(Range("Ark1!" & s1), Range("ark1!" & s1)) Me.combo_timer.List = rng3.Value End Sub Private Sub combo_sedatoer_DropButtonClick() Dim i As Long Dim iStart As Long Dim iEnd As Long Dim dtePrev As Date Dim iArray As Long Dim ary dtePrev = 0: iArray = 1 ReDim ary(1 To 2, 1 To 1) With Worksheets("Ark1") Me.combo_sedatoer.Clear For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row If .Cells(i, "A").Value < dtePrev Then If i < 1 Then ReDim Preserve ary(1 To 2, 1 To iArray) ary(1, iArray) = dtePrev ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address iArray = iArray + 1 End If iStart = i iEnd = i dtePrev = .Cells(i, "A").Value Else iEnd = i End If Next i ReDim Preserve ary(1 To 2, 1 To iArray) ary(1, iArray) = dtePrev ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address Me.combo_sedatoer.List = Application.Transpose(ary) End With End Sub So it it the address from Me.combo_timer I want Can't use listindex Reagrds Alvin "Dave Peterson" wrote: You can use this kind of syntax: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) (still an array formula) alvin Kuiper wrote: Hi i have this in a sheet in 3 collumns A=date -B= number -C= value I want to see the value(C), for date - number Like: if i in 2 tekstboxes write the date in one and the number in the second box then i get the value From C where A=the first tekbox and B = the second tekstbox Best regards Alvin -- Dave Peterson -- Dave Peterson |
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 | |||
Lookup Ques - finding value within a string to lookup | Excel Programming |