Lookup to find first non-blank cell

[mactoolsix]

I have spreadsheet with a column (N14:N68) of numbers and blanks.
I want to use the last value in the column.

=LOOKUP(2,1/(N14:N68<>""),N14:N68)

This returns a blank answer.

I can attach a spreadsheet example with this post, but I don't see how that is possible?

Thanks!!
Mike

 

[Joe4]

This returns a blank answer.

I am guessing that means that you have some entries that appear blank, but really aren't. They may be a single (or multiple) spaces.
Note that a "space" is not the same as "blank".

To find out where this is, go to cell N69, then hit CTRL and the up arrow at the same time, and see what cell it stops at. This is what it is returning.
Check to see if there is anything in this cell (i.e. blank spaces).

 

[mactoolsix]

Joe - You are correct. Column N:14:N68 is =IF(B14=" "," ",D14+E14+H14) thus if there is nothing in B14 a blank is entered.
How can I get around this?
If there is a way to attach the spreadsheet, I can do that.

Thanks!
Mike

 

[jeffreybrown]

x-posted

Answer provided at: http://www.msofficeforums.com/search.php?searchid=233299

Please read: https://www.excelguru.ca/content.php?184

 

[Joe4]

Why not return nothing instead of returning a single space?

[COLOR=#333333]=IF(B14=" ","",D14+E14+H14)[/COLOR]

Regarding Cross-Posting, while we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

This way, other members can see what has already been done in regard to a question, and do not waste time working on a question that may already be answered.

 

[jtakw]

Joe - You are correct. Column N:14:N68 is =IF(B14=" "," ",D14+E14+H14) thus if there is nothing in B14 a blank is entered.
How can I get around this?
If there is a way to attach the spreadsheet, I can do that.

Thanks!
Mike

Hi,

Start by correcting this formula to:

=IF(B14="","",D14+E14+H14)

Remove the SPACE " " in the result, but what's in B14, does Another formula put a SPACE " " in there also?