lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

alvin Kuiper - 21 Feb 2006 09:58 GMT

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

Reply to this Message

Dave Peterson - 21 Feb 2006 15:10 GMT

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)

> Hi
> i have this in a sheet
[quoted text clipped - 10 lines]
> Best regards
> Alvin

Signature

Dave Peterson

Reply to this Message

alvin Kuiper - 21 Feb 2006 15:40 GMT

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

> You can use this kind of syntax:
>
[quoted text clipped - 35 lines]
> > Best regards
> > Alvin

Reply to this Message

Dave Peterson - 21 Feb 2006 16:58 GMT

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

> Hi dave
> thanks
[quoted text clipped - 118 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message