Formula to find last used cell

Y

Yogi_Bear_79

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
 
B

Biff

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
 
Y

Yogi_Bear_79

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

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.
=LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F65535)
Click to expand...

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
 
Top