I am writing a ledger program, where the results in some columns depend on cells in the lowest row being filled in, and other cells from the next row up. When I copy these functions down a whole column, I get values down the whole column, even though I would like these functions to only trigger for the row I’m in. How is that done?
(Probably didn’t understand correctly.)
As I understand this it’s against the basical concepts of spreadsheets. Formulae are recalculated based on rules you cannot change.
To outwit these rules you have to actually write a program instead of relying on spreadsheet automatisms. In principle you can do so “inside” Calc, and then use parts (cell ranges) of the sheets only as I/O interface for your code.
Better: Move your task to a database.
I found an answer to this difficulty that works very well. A ledger program gets filled in line by line, and what I do is use a function like IF(ISBLANK(A3),"",IF(…)). That way if the first column (the date column) has no entry, it gets “”, i.e., nothing. Then the IF statement within the IF statement (or other calculation required) can do what needs to be done in the active row. Works very well, and is quite simple.