Lost in operations

misterconrad · May 21, 2014

Hi there,
Over the years, I have created and tinkered with a spreadsheet table to keep track of my checking account. I’ve since copied the thing and use it for my savings account as well. It’s pretty set; I haven’t made any changes (other than cosmetic) to it for a couple years now. And it all works with no VB; it’s all in the cell formulae.

So I’ve decided to try to make a new, improved version that integrates the tables, so to speak.

So, I have two tables: Table 1 (Checking Account); and Table 2 (Savings Account) (Actually there are 20 accounts tables all identical, arranged side by side on the same worksheet. –But, for the sake of example, I have two tables). I have a floating userform with buttons that navigate to the line at the end of the corresponding account table. I already have the Tables set up with their formulas and such so as to automatically do the math for both the Posted Balance (this should match whatever the bank says I got) and the Operational Balance (which takes into account transactions I made that have not posted at the bank yet) vis-à-vis the Credits and Debits columns. The table headings are the same among the two tables, and include “Transaction Date”, “To/From”, “Debits”, “Credits”, and – the first column in the table – “Coding Column” – which is a column I added to differentiate among various types of transactions. These are the column headings that are involved in the matter at hand.

–Which brings me to the reason for this post. One of those types of transactions is a transfer between the accounts. For simplicity’s sake, there are only two types for now: “From Checking to Savings”; and “From Savings to Checking”. For the sake of this post, I’ll be referring to the “From Checking to Savings” scenario – not the other way ‘round.

So, issue the first (but not the main reason for this post) is that instead of having to type “Transfer from Checking to Savings” in the “To/From” cell, I’d rather be able to type “312” in the “Coding Column” cell (“3” to indicate a transfer-type transaction; “1” indicating the “FROM” account involved in the transfer; and “2” indicating the “TO” account. –A transfer from Savings to Checking would have “321” in the Coding Column. ), and have that action trigger the automatic filling-in of the corresponding “To/From” cell. One of the hurdles I’ve been trying to figure out is how to reference the cells by TableName/Column Heading(a table reference) rather than Row/Column (a cell reference).

Now, normally, a transfer between two accounts requires two entries: A deduction in the “FROM” account and an increase in the “TO” account. The thing I really want, and the main reason for this post, is for the computer to look at, say, Table 1, and note the row number for every entry in the “To/From” column that = “Transfer from Checking to Savings”.

Then the computer is to take the first of those row numbers, look at and memorize the values in that row for the “Coding Column”, “Transaction Date”, “To/From”, “Debits”, and “Credits” entries, and search the Savings Account Table (Table 2) for a row in which the first three of those entries are an exact match and also the last two entries are swapped (the “FROM” account loses money – a debit, while the “TO” account gains money – the credit). If there exists such a row in the “TO” table, then fine; do nothing; END. However, if there IS NOT a match, then auto-generate one so that future occurrences of this routine will, at least for this particular record, result in fine; do nothing; END.

Then do it all over again for the next row number that has the “To/From” cell = “Transfer from Checking to Savings”. –And on and on until the computer has ensured that every transfer has entries in both tables.

Not only do I want this as a sort of built-in double-check against mistakenly entering amounts in the wrong columns, but with such a method, I would only have to input one entry for the entire transfer.
--Or am I totally over-thinking it all? Is there an easier/simpler way to accomplish:
- ensure that every transfer has entries in both tables.
- built-in double-check against accounting errors
- have to input only one entry for the entire transfer
??? I’m a bit lost in all the operations I want the computer to do, and thinking it through linearly is getting me more and more confused with every step I go forward. I’m not even sure where to start anymore. Is there anyone who can straighten me out so I fly right? Where should I begin and which direction should I go? I’ve looked into using Vlookup, Index-Match, and I’m sure I’ll need to throw in a For-Next loop or two. If I’m thinking it correctly, I’ll have to use some sort of lookup function in combination with the AND function - but how? I got no clue. And in what order? And what am I missing because I haven’t considered it? Yikes!
Thanks TONS to any/all those who help me sort this out. This is not the usual “…I know there must be some LITTLE thing I’m missing somewhere…” (- which it never is, I know). This is a buncha stuff. I think of how proud I would feel if figured out the solution all by myself. But at this point, I’d be proud of any ability I might have in understanding a solution that magically fell out of the sky. I humbly appreciate the help. While I AM smarter than a fifth-grader, I am NOT the Excel genius I like to think I am.

shg · May 21, 2014

I expect that most of this is for the joy of using Excel, but I think you're about to cross the threshold from straightforward to kinda messy. And to me, code that moves around reconciled financial transactions is a little scary.

If you could find a different outlet for your Excel passion, you could move your accounts to Quicken, and then do account transfers (and a thousand other things) without reinventing the wheel.

misterconrad · May 21, 2014

Thanks for the warning, but I think that as long as I'm down the rabbit hole, so to speak, I oughtta keep goin' to see if I can get any closer to my goal. Any tips on what operations I should try to tackle first?

shg · May 21, 2014

I don't; the effort would be massively disproportionate to the value.

Lost in operations

misterconrad

New Member

Excel Facts

shg

MrExcel MVP

misterconrad

New Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Lost in operations

misterconrad

New Member

Excel Facts

shg

MrExcel MVP

misterconrad

New Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock