Simple Excel Question

[Maurice]

I have a table like this

A B C D E F
INVOICE# DATE NAME SALE Amount Rec/Deposit BALANCE
101 8/1 Mike $500 $200 $300
102 8/2 Mary $100 $100 0
103 8/3 John $200 $100 $100
..
..
9/1 Mike $300 0

When Mike comes in on 9/1 to pay off his $300 balance from invoice #101 on
8/1. I would like to record the payment and the fact the the balance for Mike
is now $0 on this spreadsheet.
How do I look up the balance for Mike? Either based on the invoice #, Name,
I don't care. Eventually the list will contain 100s of transactions so I
can't look it up manually


Please

 

[Bernard Liengme]

We might play with some SUMIF formula but in the long run you will be better
off learning to use Pivot tables. These sites will help
Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx


I am a little concerned that Mike's 9/1 payment is in the SALES column but
this could be the result of the terrible problem we all have displaying data
in emails
best wishes

 

[Jacob Skaria]

Col A Col B Col C Col D Col E Col F
INVOICE# DATE Name SAmount Deposit Balance
101 1-Aug Mike $500 $200 $300
102 2-Aug Mary $100 $100 0
103 3-Aug John $200 $100 $100
101 300

The below formula will return the balance (in Col F) as soon as you type the
invoice number. In the above table the formula to return the balance in cell
F5 (which returns 300)is

=IF(A5="","",IF(E5="",SUMIF($A$1:A4,A5,$D$14)-SUMIF($A$1:A4,A5,$E$1:E4),SUMIF($A$1:A5,A5,$D$15)-SUMIF($A$1:A5,A5,$E$1:E5)))
(all in one line)

--If invoice number is blank..F5 will be blank
--If invoice number is keyed and deposit amount is keyed ;then the formula
recalculates the new balance
--If deposit amount is blank then the formula returns the existing balance

Try with the above table as a sample and then adjust to suit your
requirement. Try and feedback....

 

[Maurice]

Jacob,

That is perfect! Exactly was I was looking for.... I would love it if you
could help me with one small improvement. When I enter a new payment for
invoice X, I would like the previous entry in the balance column for that
invoice to be '0', so that I can keep a running total of the balance owed,
plus the deposits which should equal the original sale amount... does that
make sense?

 

[Jacob Skaria]

Maurice, that is a bit more easy. In F2 use the below formula and copy down
as required...Try and feedback

=IF(A2="","",IF(E2="",SUMIF(A:A,A2,D)-SUMIF(A:A,A2,E:E),SUMIF(A:A,A2,D)-SUMIF(A:A,A2,E:E)))

If this post helps click Yes

 

[Maurice]

Jacob,
Almost what I need. The balance column now reflects the new payment, however
this is what I need

Invoice Name Amt Pmt/Dep Bal
100 Mike 500 300 200

- --------------------------------------------------
500 (300 + 200) = 500

After I enter the new 200 payment from Mike I would like to see this:


Invoice Name Amt Pmt/Dep Bal
100 Mike 500 300 0
100 Mike 200 0

 

[Maurice]

Jacob,

The balance column shows the running balance for every row where the invoice
# matches, I only want to keep track of the remaining balance in the last row
for that invoice #

Maurice