I would like to set a cell todisplay the value from another cell on a
differant sheet, in the same workbook. I need it to look in a column range
from F2:F14. It should select the data from the last cell used that does'nt
contain a zero. I know how to do a basic IF statement, but not sure how the
syntax would work on this one. I would prefer to keep this one a formula
versus a VBA macro

On Fri, 24 Sep 2004 03:04:42 -0400, "Yogi_Bear_79"
wrote:

I would like to set a cell todisplay the value from another cell on a
differant sheet, in the same workbook. I need it to look in a column range
from F2:F14. It should select the data from the last cell used that does'nt
contain a zero. I know how to do a basic IF statement, but not sure how the
syntax would work on this one. I would prefer to keep this one a formula
versus a VBA macro


With your range named "rg", this *array-formula* will work:

=INDEX(rg,MAX((rg0)*ROW(rg))-ROW(rg)+1)

To enter an *array-formula*, after typing or pasting it into the formula bar,
hold down <ctrl<shift while hitting <enter. XL will place braces {...}
around the formula.

Note that it is not necessary to NAME rg. I wrote it that way so the formula
can be more easily generalized to other ranges.


--ron

You could simplify this

=INDEX(Sheet1!F1:F14,MAX((Sheet1!F2:F140)*ROW(F2: F14)))

Entered with Ctrl+Shift+Enter

by starting the first argument of index in row 1, you don't need to subtract
anything. It only uses F1 as an anchor - it would not return anything from
F1 unless there are no values in F2:F14 greater than 0
--
Regards,
Tom Ogilvy

"Yogi_Bear_79" wrote in message
...
I would like to set a cell todisplay the value from another cell on a
differant sheet, in the same workbook. I need it to look in a column
range
from F2:F14. It should select the data from the last cell used that
does'nt
contain a zero. I know how to do a basic IF statement, but not sure how
the
syntax would work on this one. I would prefer to keep this one a formula
versus a VBA macro

Tom,

Thanks for your time, your suggestions works great. I have one more
twist to add. In some instances the data in cells F2:F14 may currently be
blank. When that happens the formula returns the header row cells name
versus a zero. I tried to incorporate an IF statement but it always returns
false in my tests. Probably becuase I am testing for the header cells value
which is text.



"Tom Ogilvy" wrote in message
...
You could simplify this

=INDEX(Sheet1!F1:F14,MAX((Sheet1!F2:F140)*ROW(F2: F14)))

Entered with Ctrl+Shift+Enter

by starting the first argument of index in row 1, you don't need to
subtract
anything. It only uses F1 as an anchor - it would not return anything
from
F1 unless there are no values in F2:F14 greater than 0
--
Regards,
Tom Ogilvy

"Yogi_Bear_79" wrote in message
...
I would like to set a cell todisplay the value from another cell on a
differant sheet, in the same workbook. I need it to look in a column
range
from F2:F14. It should select the data from the last cell used that
does'nt
contain a zero. I know how to do a basic IF statement, but not sure how
the
syntax would work on this one. I would prefer to keep this one a formula
versus a VBA macro