Wanting to find the last non-empty cell in a column

[roddy]

Edit: Split from [Solved] How to find the last occupied cell in a column — MrProgrammer 2020-04-12 15:54 UTC

I've been looking for this formula to use in a checkbook spreadsheet I'm working on. I've been wanting to find the last non-empty cell in a column (contains the balance at that moment) so that I can reference that amount in another cell to label it "Checkbook Balance". But, since the column contains the result of the rows as a formula (Balance-Debit+Credit), the formula I want in "Checkbook Balance" in cell H8 is: =LOOKUP("ZZZ";J12:J100), it won't work.

The next issue I have is not being able to have another cell labeled "Cleared Transactions Balance" (trying this formula: =SUMIF(F12:F70;"C";J100) in which its value must be derived from the following steps:
1) search down a column labeled "C" (for cleared transactions) to find only those rows with "C"
2) then, get the amount in a cell labeled "Opening Balance" subtract the sum of all cells with a "C" that are in the "Debit" column then add the sum of all cells with a "C" that are in the "Credit" column.

This way I can have only the actual balance of those transactions that have cleared, while in the other cell I can have the total (including non-cleared transactions) in the "Checkbook Balance" cell.

I hope someone here can guide me. I've tried various formulas and none work. I've tried INDEX and COUNTA formulas, as well as MAX(ISNUMBER(H12:H65536)*ROW(H12:H65536)), etc.

In God's Harmony

[RusselB]

Can you upload a small anonymized version of your spreadsheet?
Help on uploading/attaching and anonymizing the spreadsheet is available in this tutorial
The data is of no importance, but the actual layout and formulas you use are of great importance when trying to resolve a query like this.

[Villeroy]

Since 40 years, the LOOKUP function in all spreadsheet applications does not do what 90% of users expect it to do. LOOKUP is the wrong function for anything you try to do when trying to do accounting on spreadsheets. Accounting on sheets is the root of epic fail. I have never seen a spreadsheet where accounting actually works. Professional software may be able to dump results into spreadsheet files for statistic and prognosis but no professional will ever use a spreadsheet for storage and processing.

[Lupp]

Referencing cells based on a criterion other than the address should be avoided anyway. Though "possible/feasible" in many cases, there will be a kind of expense spoiling the efficiency of your sheet and making your formulas complicated and difficult to check for correctness.
If you insist on referencing something like "last used cell in that column" or "used range..." you always need to give an assurance that the "super-range" you think of is filled without gaps. Otherwise things get complicated in an unreasonable way.
However, given assurances can be wrong due to accident, inadvertent disregard or whatever. In addition cells may look blank without being ...

Therefore: Where feasible avoid the need of references of the mentioned kind!

Is it feasible in your case?
If your sheets (or the columns you are concerned about) are expected to contain amounts and a total, it's very simple: Place the row for totals above the details. That's far better anyway under additional aspects.
You may then consider in addition to re-order your rows (surely containing a date) regarding "most recent on top". Every time you need a new row you can insert it then into a very convenient place, and, if you regarded a few rules concerning the design, your formulas will adapt automatically as needed.

[roddy]

The advice and quick response from all of you is greatly appreciated. It was not my intention to describe what I'm trying to achieve in a complex way.
The =OFFSET(D2,COUNTA(D:D)-1,0) formula also did not work.
I'm trying to tweak the spreadsheet to be capable of what I described.
I will upload a generic version of my sheet with comments to help explain.

In God's Harmony

[lader]

Try it now:

_Checkbook Register [TEST].ods

(26.71 KiB) Downloaded 174 times

[roddy]

Well, I found a partial solution for this spreadsheet for the "Checkbook Balance" cell. Since no cell in that column will be blank (would be nice to figure the formula to give the result when there are multiple blank cells in that column), I used:
=OFFSET(J12;COUNT(J12:J1000)-1;0;1;1)

This comes from a post answered by mahfiaz at: https://ask.libreoffice.org/en/question ... -a-column/

In God's Harmony

[lader]

Still added...Cell H6 modified:

_Checkbook Register [2].ods

(26.69 KiB) Downloaded 315 times

[roddy]

lader,

Thank you so much! I owe you for this

NOTE: When entering the curly brackets manually it reverts to without them, so I had to copy and paste special from your array formula. Making a change and putting it back then pressing Cntrl-Shift-Enter didn't do anything either. Still didn't make it an array formula. Not sure why.

Can this same array formula be altered to still give the final cell value in the column when there are multiple discontiguous blank cells? Just curious.
Again, thank you and all here. I pray the Lord to keep you & your families healthy and safe!

In God's Harmony