Selecting Last Populated Cell in a Range

[gawa_ram]

All

A spreadsheet I'm doing for work requires me to use the last drawing of fuel from a driver to calculate the MPG for each unit in the fleet.

I was looking a basic LOOKUP formula which checked only one column. However, the spreadsheet has evolved and I now require the LOOKUP formula to check columns and rows (so I6:K37) for the last populated cell.

I amended the LOOKUP formula to contain this and instead of picking up the last populated cell in J17 for example, the formula returns the value 0 as K37 is blank.

This is the only thing the spreadsheet needs to be finished and any help would be greatly appreciated!

Thanks

 

[MrKowz]

I'm not quite sure I understand what you are asking. Can you please give us an example of your input, how your sheet is laid out, and desired output?

 

[Rick Rothstein]

I just know there must be a simpler formula to do this, but my mind has gone blank at the moment and this is all I could come up with...

=INDEX(A1:K37,SUMPRODUCT(MAX((I6:K37<>"")*ROW(I6:K37))),SUMPRODUCT(MAX((I6:K37<>"")*(ROW(I6:K37)=SUMPRODUCT(MAX((I6:K37<>"")*ROW(I6:K37))))*COLUMN(I6:K37))))

So, until someone comes along with that simpler formula, you are going to have to make do with the above. However, if you are willing to entertain the use of a UDF (user defined function), then I can help you out (VBA is more my "thing" than formulas are)...

Function LastData(Rng As Range) As Variant
  LastData = Rng.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Value
End Function

If you have never installed a UDF, it is easy. Press ALT+F11 from any worksheet to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that opened up. That's it... you can now use LastData just like any built-in Excel formula. Go back to your worksheet and put this in any cell (not in I6:K37)...

=LastData(I6:K37)

and the formula will display the last data value in that range.

 

[gawa_ram]

I just know there must be a simpler formula to do this, but my mind has gone blank at the moment and this is all I could come up with...

=INDEX(A1:K37,SUMPRODUCT(MAX((I6:K37<>"")*ROW(I6:K37))),SUMPRODUCT(MAX((I6:K37<>"")*(ROW(I6:K37)=SUMPRODUCT(MAX((I6:K37<>"")*ROW(I6:K37))))*COLUMN(I6:K37))))

So, until someone comes along with that simpler formula, you are going to have to make do with the above.

Worked a dream - now to work this into my formula to calculate the MPG!

Many thanks Rick.

 

[Rick Rothstein]

You are quite welcome 'gawa_ram'. I'm not sure if you saw (or even care about) my edited comments regarding a UDF solution. The only reason I mention it is it would be easier to use directly in another formula if that is what you are ultimately doing (it would not require a helper cell to hold that last data cell's contents).

 

[gawa_ram]

No Rick - I happened to click back out of thanking you and saw your edit about the UDF - works even better!

Thanks again.