Help with VLookup

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

I have an excel file that has four columns: account
number, account description, Debit & Credit columns. The
debit and credit columns are filled in automatically from
our ledger system. I also have a reporting file that I
take the figures in the first excel file and manually
input them into the reporting file. The reporting file
has three columns: account number, account description and
amount. The credit amounts in the first file are inputed
in the second file as negatives.

How can I write a formula in the second file to pick up
the figures in the first file? I have tried VLookup,
however, the way I understand the function, it will only
look up one column in an argument.

Help!
 
Thanks for your help, Eric, however, I have to get all the
data in columns C & D from from worksheet to column C in
another worksheet. Is there another way, other than
VLookup?
 
Dale,

I think you might be looking for something like a SUMIF

I assume you have multiple transactions on your first
worksheet... ie

Acct# Desc. Debit Credit
1111 ---- 400
1111 ---- 200
1111 ---- 140
2121 ---- 150
2121 ---- 225

And on your next sheet you would like
Acct# Desc. Amt
1111 ---- 340 (ie 400 - 200 + 140)
2121 ---- 75 (ie -150 + 225)

Sumif would be as follows
Debits (column C)
=SUMIF([Book1.xls]Sheet4!$A$1:$A$100,"1111",[Book1.xls]Sheet4!$D$1:$D$100)
Credits (column D)
=SUMIF([Book1.xls]Sheet4!$A$1:$A$100,"1111",[Book1.xls]Sheet4!$C$1:$C$100)

Combining
=SUMIF([Book1.xls]Sheet4!$A$1:$A$100,"1111",[Book1.xls]Sheet4!$D$1:$D$100) -
SUMIF([Book1.xls]Sheet4!$A$1:$A$100,"1111",[Book1.xls]Sheet4!$C$1:$C$100)

= All debits for 1111 - all credits for 1111

Dan E
 
Back
Top