Excel Help

finnith

Woo accounting!

So I need some excel help guys, and I'm unfortunately not too skilled with VBA (though I'm working on that).

So our organization has two funds, an operating and fundraising account, balances of which I need to keep track of. I've got a general ledger that I use to input all the transactions, each of which has a numerical transaction ID. In order to track the account balances I have in a second sheet a set of columns for each of the two accounts. The first column in for each has a list of transaction IDs which relate to transactions that involve that account, and the rest have vlookup formulas that autofill data about the transaction based on the ID. For example, if transaction "5" involved a payment of $30 for party expenses out of our operating account, I would input "5" in the left column. The second column will lookup 5 in the general ledger and retrieve the date, the third the description, the fourth the debit amount, and the fifth the credit amount.

The problem of course is that I have to manually input the ID's. I'd really love Excel to automate this process such that it automatically retrieves the transactions ID's and information for that account as I enter data into the general ledger, which I'd like to be the only sheet in which I'm doing any data entry. I also don't like how I have to drag down the formulas every time a new row is added due to there being another transaction, but I suppose I can automate that just by making use of the format as table feature.

Can you guys help me out? I'm sorry if the problem or my description is not clear, in which case I'll just post up a xls(x), which will be much easier to understand.

pirateluigi

Without seeing the exact page, I'm not sure if I really understand the problem. Here's how I interpret it:
You input a transaction ID into cell A5 of sheet1. You want cell A5 of sheet2 to automatically = the value of cell A5 in sheet1?

If so, you can just put a conditional formula into the A column cells of sheet2, like this: =IF(Sheet1!A1<>"",Sheet1!A1,"")
That would, if the matching cell in sheet1 isn't blank, copy over that data into sheet 2. You could even make the vlookup conditional like this: =IF(A1<>"",VLOOKUP(A1,Sheet1!A:B,2,FALSE),"")

tyrannus

I'm a CPA and I love Excel as much as you do but this would, after a few quarters, sound like a nightmare to reconcile. Like, it sounds like you're using a disbursements journal but trying to have disbursements from both accounts all in the ledger.

Why not just run these transactions through the register in QuickBooks? You can journal in your reconciled balances from last year. You can also give them classes to help further identify the transactions.

schattenjaeger

pirateluigi wrote: »

Without seeing the exact page, I'm not sure if I really understand the problem. Here's how I interpret it:
You input a transaction ID into cell A5 of sheet1. You want cell A5 of sheet2 to automatically = the value of cell A5 in sheet1?

If so, you can just put a conditional formula into the A column cells of sheet2, like this: =IF(Sheet1!A1<>"",Sheet1!A1,"")
That would, if the matching cell in sheet1 isn't blank, copy over that data into sheet 2. You could even make the vlookup conditional like this: =IF(A1<>"",VLOOKUP(A1,Sheet1!A:B,2,FALSE),"")

I'm not exactly sure what he's describing, but this was my first thought, but it requires that the rows stay lined up sheet to sheet, and since a given transaction is not guaranteed to have components from both accounts, this isn't guaranteed.

Tell me if I have this right (understanding I'm not an accountant and my jargon may not be quite right):

You have, essentially, a database of transactions, uniquely identified by a transaction ID. Each transaction has certain attributes (date, credit amount, debit amount, etc.). What I'm unsure about is...does each transaction potentially affect both Account A and Account B balances? Or, does each transaction only affect one balance? My impression is it is the first.

So Transaction 1 may have, say, a 30 dollar debit to account A and a 40 dollar debit to account B? And you want to keep track of the balance in both accounts?

It seems what you're doing is basically making the second sheet a query of that database, and returning a listing of the transactions for each account. Do you actually care about this? For purposes of tracking a balance, can't you just sum down the columns that represent the debit/credit to accounts A and B, and add that result to the starting balance (if it isn't 0)? If you do care about seeing the specific transactions for (example) Account A, can't you just filter out the "blank" Account A entries in the main data sheet whenever you want to see them (and the blanks for Account B when you want to see those?) Alternatively, you can enter the data into a database tool (like Access) and just actually query it (as my use of words have implied), but if you don't know how to use something like that and don't want to or don't have time to learn, Excel can work. However, even in Access, you could just filter the table too (filtering is, in essence, a query).


On the other hand:

If each transaction represents a debit/credit to only a single account, and you simply indicate the account in another column, then you can simply do a sumif formula. =sumif(column representing account names,target account name or ID,column representing values) and it will sum up the values only for the target account.

If you want to view the transactions that go with a given account, you can again do the filtering. My thought is you're trying to create the second sheet as an unnecessary step in performing the sumif.