formula to find last value in a list on another worksheet

[Garry T]

Following is an excerpt from a checkbook. I'm on another worksheet or in another workbook, and would like the last value in the checkbook automatically updated. For ex, if I'm on a different worksheet and it's Sept 9th, I would want 37,086.67 to show w/out having to go get it. If I'm on that worksheet today, I would want that cell to read 36,778.24. There is a new line and updated balance every time a transaction is posted. Thank you

[TABLE="width: 173"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]38,217.41[/TD]
[TD="align: right"]9/9/2019[/TD]
[/TR]
[TR]
[TD="align: right"]38,079.54[/TD]
[TD="align: right"]9/9/2019[/TD]
[/TR]
[TR]
[TD="align: right"]37,921.09[/TD]
[TD="align: right"]9/9/2019[/TD]
[/TR]
[TR]
[TD="align: right"]37,674.78[/TD]
[TD="align: right"]9/9/2019[/TD]
[/TR]
[TR]
[TD="align: right"]37,411.32[/TD]
[TD="align: right"]9/9/2019[/TD]
[/TR]
[TR]
[TD="align: right"]37,086.67[/TD]
[TD="align: right"]9/9/2019[/TD]
[/TR]
[TR]
[TD="align: right"]35,377.24[/TD]
[TD="align: right"]9/10/2019[/TD]
[/TR]
[TR]
[TD="align: right"]36,355.15[/TD]
[TD="align: right"]9/11/2019[/TD]
[/TR]
[TR]
[TD="align: right"]36,778.24[/TD]
[TD="align: right"]9/11/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

You can use a reverse LOOKUP, as shown here: https://exceljet.net/how-to-lookup-first-and-last-match

So, if your data is in columns A and B, and the date you are looking up is in cell F1, the formula would look like:

=INDEX(A:A,MATCH(F1,B:B,1))

 

[Garry T]

Thanks for your reply, Joe! I've used this forum several times and I'm never very good at asking my questions. The first picture is cell E2 on sheet1: [TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 78, align: left"]Debit (credit)[/TD]
[TD="class: xl65, width: 91, align: left"] 35,878.24[/TD]
[/TR]
</tbody>[/TABLE]


The next picture is the running balance of my checkbook. I want cell E2 on sheet1 to always show the current (i.e., last balance) on the checkbook.
[TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 87, align: right"]36,186.67[/TD]
[TD="class: xl66, width: 91, align: right"]9/9/2019[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]34,477.24[/TD]
[TD="class: xl66, align: right"]9/10/2019[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]35,455.15[/TD]
[TD="class: xl66, align: right"]9/11/2019[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]35,878.24[/TD]
[TD="class: xl66, align: right"]9/11/2019[/TD]
[/TR]
</tbody>[/TABLE]
notice how the last balance 35,878.24 shows in cell E2 on sheet1, except that right now I'm doing it manually. I guess maybe I would want to enter a formula in cell E2 that finds the last value in the list of checkbook balances.

Thanks again!

 

[Joe4]

Try:

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

as shown and explained here: https://exceljet.net/formula/get-value-of-last-non-empty-cell

 

[Garry T]

That worked! Thank you very much, Joe!

I ended up using ISNUMBER after I clicked your explanation link! Thanks again!

 

[Joe4]

You are welcome.