I need a formula for the following:

Given range BA2:BL2, I need to find the first value in this range 0,
reading left to right, and then return the text in the corresponding cell in
the range BA1:BL1.

Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the
text in BD1 returned.

Clear?

I hope so.

Thanks.

Dave
--
Brevity is the soul of wit.

Give this a whirl. You can use it in code or as a UDF...

Sub whatever()
MsgBox FindStuff(Sheets("sheet1").Range("BA2:BL2"))
End Sub

Public Function FindStuff(ByVal rngToSearch As Range) As String
Dim rng As Range

FindStuff = "No Value Found"
For Each rng In rngToSearch
If rng.Value < 0 Then
FindStuff = rng.Offset(-1, 0).Value
Exit For
End If
Next rng
End Function
--
HTH...

Jim Thomlinson


"Dave F" wrote:

I need a formula for the following:

Given range BA2:BL2, I need to find the first value in this range 0,
reading left to right, and then return the text in the corresponding cell in
the range BA1:BL1.

Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the
text in BD1 returned.

Clear?

I hope so.

Thanks.

Dave
--
Brevity is the soul of wit.

Interesting.

Let's say I also want to do a similar search for range BA3:BL3 but still
return the value in range BA1:BL1. Would I change the rng.Offset(-1,
0).Value to rng.Offset(-2, 0).Value ?

Dave

--
Brevity is the soul of wit.


"Jim Thomlinson" wrote:

Give this a whirl. You can use it in code or as a UDF...

Sub whatever()
MsgBox FindStuff(Sheets("sheet1").Range("BA2:BL2"))
End Sub

Public Function FindStuff(ByVal rngToSearch As Range) As String
Dim rng As Range

FindStuff = "No Value Found"
For Each rng In rngToSearch
If rng.Value < 0 Then
FindStuff = rng.Offset(-1, 0).Value
Exit For
End If
Next rng
End Function
--
HTH...

Jim Thomlinson


"Dave F" wrote:

I need a formula for the following:

Given range BA2:BL2, I need to find the first value in this range 0,
reading left to right, and then return the text in the corresponding cell in
the range BA1:BL1.

Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the
text in BD1 returned.

Clear?

I hope so.

Thanks.

Dave
--
Brevity is the soul of wit.

That would do it... If you need it to be dynamic then use something like
this...

Public Function FindStuff(ByVal rngToSearch As Range) As String
Dim rng As Range

FindStuff = "No Value Found"
For Each rng In rngToSearch
If rng.Value < 0 Then
FindStuff = Cells(1, rng.Column).Value
Exit For
End If
Next rng
End Function
--
HTH...

Jim Thomlinson


"Dave F" wrote:

Interesting.

Let's say I also want to do a similar search for range BA3:BL3 but still
return the value in range BA1:BL1. Would I change the rng.Offset(-1,
0).Value to rng.Offset(-2, 0).Value ?

Dave

--
Brevity is the soul of wit.


"Jim Thomlinson" wrote:

Give this a whirl. You can use it in code or as a UDF...

Sub whatever()
MsgBox FindStuff(Sheets("sheet1").Range("BA2:BL2"))
End Sub

Public Function FindStuff(ByVal rngToSearch As Range) As String
Dim rng As Range

FindStuff = "No Value Found"
For Each rng In rngToSearch
If rng.Value < 0 Then
FindStuff = rng.Offset(-1, 0).Value
Exit For
End If
Next rng
End Function
--
HTH...

Jim Thomlinson


"Dave F" wrote:

I need a formula for the following:

Given range BA2:BL2, I need to find the first value in this range 0,
reading left to right, and then return the text in the corresponding cell in
the range BA1:BL1.

Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the
text in BD1 returned.

Clear?

I hope so.

Thanks.

Dave
--
Brevity is the soul of wit.

You could also use a formula:

=INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0))

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.)

or just in case:

=IF(COUNTIF($BA2:$BL2,""&0)=0,"No positive numbers",
INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0)))

(still an array formula)

Dave F wrote:

I need a formula for the following:

Given range BA2:BL2, I need to find the first value in this range 0,
reading left to right, and then return the text in the corresponding cell in
the range BA1:BL1.

Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the
text in BD1 returned.

Clear?

I hope so.

Thanks.

Dave
--
Brevity is the soul of wit.

--

Dave Peterson

Yeah I was thinking it should be something with INDEX and MATCH. I have to
figure those two functions out. I like how you trap errors in the second
formula.

Thanks.

Dave
--
Brevity is the soul of wit.


"Dave Peterson" wrote:

You could also use a formula:

=INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0))

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.)

or just in case:

=IF(COUNTIF($BA2:$BL2,""&0)=0,"No positive numbers",
INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0)))

(still an array formula)

Dave F wrote:

I need a formula for the following:

Given range BA2:BL2, I need to find the first value in this range 0,
reading left to right, and then return the text in the corresponding cell in
the range BA1:BL1.

Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the
text in BD1 returned.

Clear?

I hope so.

Thanks.

Dave
--
Brevity is the soul of wit.

--

Dave Peterson

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

Dave F wrote:

Yeah I was thinking it should be something with INDEX and MATCH. I have to
figure those two functions out. I like how you trap errors in the second
formula.

Thanks.

Dave
--
Brevity is the soul of wit.

"Dave Peterson" wrote:

You could also use a formula:

=INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0))

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.)

or just in case:

=IF(COUNTIF($BA2:$BL2,""&0)=0,"No positive numbers",
INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0)))

(still an array formula)

Dave F wrote:

I need a formula for the following:

Given range BA2:BL2, I need to find the first value in this range 0,
reading left to right, and then return the text in the corresponding cell in
the range BA1:BL1.

Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the
text in BD1 returned.

Clear?

I hope so.

Thanks.

Dave
--
Brevity is the soul of wit.

--

Dave Peterson


--

Dave Peterson