Formula to find last used cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yogi_Bear_79 - 20 Sep 2006 02:55 GMT

I want to populate a cell in sheet1 by going to sheet 2 column F and finding
the value of the last filled cell that is not a zero

Reply to this Message

Biff - 20 Sep 2006 05:12 GMT

I'm assuming all values in column F are numbers.

=LOOKUP(2,1/(Sheet2!F1:F65535<>0),Sheet2!F1:F65535)

You can't use entire column references in this portion:
1/(Sheet2!F1:F65535<>0).

I would use a more reasonable range size.

Biff

>I want to populate a cell in sheet1 by going to sheet 2 column F and
>finding the value of the last filled cell that is not a zero

Reply to this Message

Yogi_Bear_79 - 21 Sep 2006 03:48 GMT

Thanks, it works, however when the colum is blank it returns the column
header from F1, I'd rather it return a zero or nothing in this case.

=LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F65535)

> I'm assuming all values in column F are numbers.
>
[quoted text clipped - 9 lines]
>>I want to populate a cell in sheet1 by going to sheet 2 column F and
>>finding the value of the last filled cell that is not a zero

Reply to this Message

Biff - 21 Sep 2006 06:35 GMT

>Thanks, it works, however when the colum is blank it returns the column
>header from F1, I'd rather it return a zero or nothing in this case.
>=LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F65535)

If that's the actual formula you're using there is no possible way for it to
return a header from cell F1!

Try this to return a blank:

=IF(ISNA(LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F27)),"",LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F27))

If you want a 0 instead, replace the double quotes "" with a 0.

Biff

> Thanks, it works, however when the colum is blank it returns the column
> header from F1, I'd rather it return a zero or nothing in this case.
[quoted text clipped - 14 lines]
>>>I want to populate a cell in sheet1 by going to sheet 2 column F and
>>>finding the value of the last filled cell that is not a zero

Reply to this Message

Rate this thread: