| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple Excel Question
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple Excel Question
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/assistan...lconPT101.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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Maurice" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple Excel Question
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$1:D4)-SUMIF($A$1:A4,A5,$E$1:E4),SUMIF($A$1:A5,A5,$D$1:D5 )-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.... -- If this post helps click Yes --------------- Jacob Skaria "Maurice" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple Excel Question
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? "Maurice" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple Excel Question
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:D)-SUMIF(A:A,A2,E:E),SUMIF(A:A,A2,D:D)-SUMIF(A:A,A2,E:E))) If this post helps click Yes --------------- Jacob Skaria "Maurice" wrote: 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? "Maurice" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple Excel Question
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 ---------------------------------------- 500 500 "Bernard Liengme" wrote: 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/assistan...lconPT101.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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Maurice" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple Excel Question
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 "Jacob Skaria" wrote: 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:D)-SUMIF(A:A,A2,E:E),SUMIF(A:A,A2,D:D)-SUMIF(A:A,A2,E:E))) If this post helps click Yes --------------- Jacob Skaria "Maurice" wrote: 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? "Maurice" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple VB for Excel Question | Excel Discussion (Misc queries) | |||
| IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
| Simple excel question for newbie | Excel Worksheet Functions | |||
| excel charts, simple question | Excel Worksheet Functions | |||
| Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Linear Mode
