Hi,

I'm drawing a blank.....

Function in (N1) : =IF(M1 0, then M1) ELSE If(L1 0, then L1)
ELSE IF(K1 0, K1) etc......

Thank you.

Yovation

=IF(M10,M1,IF(L10,L1,IF(K10,K1,"")))
--
David Biddulph

"yovation" wrote in message
...
Hi,

I'm drawing a blank.....

Function in (N1) : =IF(M1 0, then M1) ELSE If(L1 0, then L1)
ELSE IF(K1 0, K1) etc......

Thank you.

Yovation

In N1 enter:

=lpv(A1:M1)

and enter this UDF:

Function lpv(r As Range) As Variant
lpv = ""
For Each rr In r
If rr.Value 0 Then
lpv = rr.Value
End If
Next
End Function

The UDF returns the first positive value starting from M1, working backwards
toward A1
--
Gary''s Student - gsnu2007c


"yovation" wrote:

Hi,

I'm drawing a blank.....

Function in (N1) : =IF(M1 0, then M1) ELSE If(L1 0, then L1)
ELSE IF(K1 0, K1) etc......

Thank you.

Yovation

Try this:

N1:
=IF(COUNTIF(J1:M1,"0"),INDEX(1:1,MAX(INDEX((J1:M1 0)*COLUMN(J1:M1),0))),"na")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"yovation" wrote in message
...
Hi,

I'm drawing a blank.....

Function in (N1) : =IF(M1 0, then M1) ELSE If(L1 0, then L1)
ELSE IF(K1 0, K1) etc......

Thank you.

Yovation

Thank you everyone for your help.

I ended up using:
=IF(M10,M1,IF(L10,L1,IF(K10,K1,"")))

It works fine for what I need.

Yovation.

Seems like something is wrong here.

I am using this in H1:
=IF(G20,G2,IF(F20,F2,IF(E20,E2,IF(D20,D2,IF(C2 0,C2,"")))))

I am working with TEXT (and maybe that is the problem).

anyway, if C2=Hi, H1 is blank?

Can someone try this formula please?

Thank you.
David

The formula will work with text, BUT, the question is ... what do you have
in the rest of the cells?

Do they perhaps contain formulas that maybe return nulls ( "" )?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"yovation" wrote in message
...
Seems like something is wrong here.

I am using this in H1:
=IF(G20,G2,IF(F20,F2,IF(E20,E2,IF(D20,D2,IF(C2 0,C2,"")))))

I am working with TEXT (and maybe that is the problem).

anyway, if C2=Hi, H1 is blank?

Can someone try this formula please?

Thank you.
David

Note:

You can nest only 7 IF's so if your "etc." exceeds that you will need something
else.


Gord Dibben MS Excel MVP

On Mon, 28 Jan 2008 10:26:19 -0800 (PST), yovation wrote:

Thank you everyone for your help.

I ended up using:
=IF(M10,M1,IF(L10,L1,IF(K10,K1,"")))

It works fine for what I need.

Yovation.

Hi,

It is the formating, but I don't know how to fix it?

Using the formula
in H1:
=IF(G20,G2,IF(F20,F2,IF(E20,E2,IF(D20,D2,IF(C2 0,C2,"")))))


if there is data in C1, D1 is preventing that data from showing up in
H1? So if I delete whatever is invisible in D1, it works. I have
tried to just copy the formating from one cell to another but that
doesn't work. So Excel thinks there is something in D1 but it's not
visible.

Thank you.
David

It's not the format of the cell that affects the result of the formula, it's
the content (even if it's only a space).
--
David Biddulph

"yovation" wrote in message
...
Hi,

It is the formating, but I don't know how to fix it?

Using the formula
in H1:
=IF(G20,G2,IF(F20,F2,IF(E20,E2,IF(D20,D2,IF(C2 0,C2,"")))))


if there is data in C1, D1 is preventing that data from showing up in
H1? So if I delete whatever is invisible in D1, it works. I have
tried to just copy the formating from one cell to another but that
doesn't work. So Excel thinks there is something in D1 but it's not
visible.

Thank you.
David