I need a Calc macro which will move all cells in four contiguous columns – except those in the first two rows – down one row, and then copy/extend the formula from a cell in what is now the 4th row up to the now empty cell directly above it.
I know: That’s a confusing explanation. Clearer details, images, and attached example spreadsheets follow. But my basic problem is that while I can perform the required actions using the mouse, I can’t figure out how to do it using only the keyboard, and if I understand correctly this means I can’t use the macro recorder to create such a macro. While I have extensive programming experience, the learning curve for the macro APIs is very steep, and I’m hoping some knowledgeable and kind person can help me with what is probably a simple task for them.
I keep my personal finances in a very simple, very ordinary Calc spreadsheet, for example:
oldest_at_top.ods (26.9 KB)
I always keep a horizontally split view with the first two header rows visible, including a very clever formula that someone here provided to me (sorry, forgot who and when, but thanks again to whoever it was) for example in the D1 cell: =OFFSET(D3,COUNT(D3:D1000)-1,0,1,1) which finds the most recent balance in column D and displays it in the header area.
The spreadsheet has worked well for many years, but the above formula only partly solves what’s become an increasingly annoying usage problem. In contrast to this simple example, the real spreadsheet has many more accounts spanning additional columns, and the accounts have widely differing numbers of rows in them. Some have a few dozen while others have many hundreds. The split view and the clever formula means I can scroll horizontally to check all the accounts’ current balances. But to add new entries to an account I have to manually scroll up and down to find the first (lowest) blank row, and then repeat the process for other accounts. Note that I do not want to keep each account on a separate sheet (which I could keep scrolled to the bottom) – being able to quickly scroll horizontally to check the all balances is an important feature.
As time has gone on and the spreadsheet has grown and become increasingly difficult to use, it’s occurred to me that what I really need is to reverse to order of the columns so that newest entries are at the top:
newest_at_top.ods (26.4 KB)
This eliminates the need for the split view and clever formula because the current balances are always visible at the top (in row 3). It’s also additionally better because having the more recent history visible is more useful.
But it presents a different problem, the need for a macro as described at the start of this overly long post. To add a new entry I have to select columns F through I (for example), then do Edit -> Select -> Select Data Area (or use CTRL+*), then use CTRL+mouse to de-select F1 through I2, then mouse-drag the entries down one row, and then click on I4 to drag its formula up to I3 before finally being able to enter a new transaction in F3 through H3. (If it weren’t for Select Data Area I’d have to manually select from F3 through I3 down to the oldest entries, which would be right back to the current problem in oldest_at_top.ods.)
Again, I’d be grateful for any help creating a macro to do this, either a way to do all the actions without using the mouse if that would allow Record Macro to work, or explicit macro code otherwise. Bonus points if the code is in Python which I’m fairly experienced with – I haven’t programmed in Basic for more decades than I care to admit, but that would be OK too (either to use blindly as-is or as a starting point for tweaking/hacking).
Regardless, thanks to anyone who’s read this far even if you can’t provide any help with my requests.
. This should never override an explicitly set format (or CF).
