Last cell populated only

[petiteshiraz]

In columns B through M I have information that corresponds to data for each month. When January comes, I enter that month’s data, when February comes, I do the same.

In column N, I want to have only the info from the last cell that is filled in. In other words, if there is data in cells B4, C4, and D4, I want cell N4 to be populated with the data from cell D4.
How do I do this?

 

[AD_Taylor]

Try this. The formula below should be entered into cell N1, so all formulas are relative to that row number.
It should work for any text and numbers entered into columns B:M as long as all cells are filled. If you for example enter B1, then D1 it will not work. The cells must be filled in sequence.

=IF( COUNTIF($B1:$M1, "?*") + COUNT($B1:$M1) = 0, "No Data!", INDIRECT(ADDRESS(ROW($N1), COUNTIF($B1:$M1, "?*") + COUNT($B1:$M1) + COLUMN($B1) - 1)))

 

[Dryver14]

If you are entering numbers in these cells this should do

=LOOKUP(9.99999999999999E+307,B4:M4)

 

[petiteshiraz]

That works!!!!

Thank you so much.

 

[Rick Rothstein]

ADVERTISEMENT

If you are entering numbers in these cells this should do

=LOOKUP(9.99999999999999E+307,B4:M4)

That works!!!!

Thank you so much.

This "simpler" formula will work whether the contents of the cells are text or numbers...

=LOOKUP(2,1/(B4:M4<>""),B4:M4)

 

[Jonmo1]

This "simpler" formula will work whether the contents of the cells are text or numbers...

=LOOKUP(2,1/(B4:M4<>""),B4:M4)

In what way is that "simpler" ?
Shorter...Yes
But I wouldn't say Simpler.

The first will be more efficient, given the data is numeric.

 

[Rick Rothstein]

ADVERTISEMENT

In what way is that "simpler" ?
Shorter...Yes
But I wouldn't say Simpler.

The first will be more efficient, given the data is numeric.

Notice I put quote marks around the word "simpler"... that was supposed to qualify the word as not being exactly like its definition (and yes, shorter might have been a better word to use).

I'm curious on you last statement... Do you know (or anyone else reading this thread), or is it documented somewhere, how much more efficient we are talking here? I mean, is the percentage difference of adding the calculation for the boolean and then dividing it into 1 to the formula marginal or is it significant?

 

[Jonmo1]

Notice I put quote marks around the word "simpler"... that was supposed to qualify the word as not being exactly like its definition (and yes, shorter might have been a better word to use).

I'm curious on you last statement... Do you know (or anyone else reading this thread), or is it documented somewhere, how much more efficient we are talking here? I mean, is the percentage difference of adding the calculation for the boolean and then dividing it into 1 to the formula marginal or is it significant?

Not sure if it's documented, I'm sure there have been discussions about it on the forum though.

But to my mind, the difference is significant.
This does a simple binary search
=LOOKUP(9.99999999999999E+307,B4:M4)
It ends up only looking at about half of the cells.

But this formula
=LOOKUP(2,1/(B4:M4<>""),B4:M4)
Must eveluate every cell in the range for <>"", AND perform a devision function on every cell in the range, Then do the same basic binary search.

That is a great formula, for mixed values of numbers and text.
But if it's known that the data is all numeric then the first is much better.

Also, if it's known that the data is all text, then a similar can be done using Rept("z",255) for the lookup value instead of BigNum.

 

[Rick Rothstein]

Not sure if it's documented, I'm sure there have been discussions about it on the forum though.

But to my mind, the difference is significant.
This does a simple binary search
=LOOKUP(9.99999999999999E+307,B4:M4)
It ends up only looking at about half of the cells.

But this formula
=LOOKUP(2,1/(B4:M4<>""),B4:M4)
Must eveluate every cell in the range for <>"", AND perform a devision function on every cell in the range, Then do the same basic binary search.

I must take some time and read up on the background to some of Excel's functions... I did not realize LOOKUP did a binary search for its "straightforward" lookups. Yeah, given that, I would agree that the difference is significant rather than marginal. Thanks for following up with me on this.

 

[Jonmo1]

I must take some time and read up on the background to some of Excel's functions... I did not realize LOOKUP did a binary search for its "straightforward" lookups. Yeah, given that, I would agree that the difference is significant rather than marginal. Thanks for following up with me on this.

No problem, that's why the data must be sorted ascending (for standard lookups), Same for VLOOKUP with TRUE, and MATCH with -1 or 1.