Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
track unpaid invoices
hi, i have a spreadsheet that lists my various invoices for my export
business. these include invoices that have been paid and invoices not paid. i then copy accross onto another spreadsheet (different tab, in the same workbook), the invoices that are unpaid. i wanted to break-down this 2nd spreadsheet client-wise, so that i know how much each clients owes me, instead of one large un-paid amount owed to me in total by every client of mine. i have read through yr help-menu and forums and understood and used the "SUMIF" formula and managed to get half a solution, where i can make a summary of the total of what each clients owes by BUT i can't get the individual invoices he owes me for. Also, my manner of doing such work is not productive as i have to manually copy data accross to my 2nd spreadsheet for un-paid invoices. any suggestion for a more productive solution ? or should i switch to access. i am synching this data with my PDA that runds MS OS and my pda has pocket excel on it but no access. awaiting yr advise thanks mukesh |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
track unpaid invoices
Hi
You needn't bother to copy the data to another sheet. Provided you have a header row on your sheet, mark the headers then DataFilterAutofilter Use the dropdown on the Field for Payment date, and select Blanks - this will show you all unpaid lines. Then use the dropdown on Customer name, select any Customer and you will see all his unpaid lines. If you want to see the totals, Insert a new row 1 and in the column where the amounts are held, say column F, enter =SUBTOTAL(9,$F$3:$F$1000) Change the range to suit, but this will give a total for all of the visible lines from your filter. -- Regards Roger Govier "mukesh" wrote in message ... hi, i have a spreadsheet that lists my various invoices for my export business. these include invoices that have been paid and invoices not paid. i then copy accross onto another spreadsheet (different tab, in the same workbook), the invoices that are unpaid. i wanted to break-down this 2nd spreadsheet client-wise, so that i know how much each clients owes me, instead of one large un-paid amount owed to me in total by every client of mine. i have read through yr help-menu and forums and understood and used the "SUMIF" formula and managed to get half a solution, where i can make a summary of the total of what each clients owes by BUT i can't get the individual invoices he owes me for. Also, my manner of doing such work is not productive as i have to manually copy data accross to my 2nd spreadsheet for un-paid invoices. any suggestion for a more productive solution ? or should i switch to access. i am synching this data with my PDA that runds MS OS and my pda has excel on it but no access. awaiting yr advise thanks mukesh |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
track unpaid invoices
HI ROGER
THANKS FOR YR EMAIL YES, YOUR SOLUTION DID HELP ME TO A CERTAIN AMOUNT BUT I STILL NEED IN INDIVIDUAL TABS, A WORKSHEET THAT UPDATES AUTOMATICALLY FROM THE MAIN SPREADSHEET AND LISTS OUT INVOICES AND THEIR AMOUNTS, FOR EACH CLIENT. SO, THERE WILL BE A TAB IN THE SPREADSHEET FOR EACH CLIENT, SHOWING THE INDIVIDUAL INVOICES AND AMOUNT OWED, WHICH UPDATES ITSELF FROM THE MAIN SPREADSHEET, THAT LISTS ALL INVOICES, PAID AND UNPAID. PLS ADVISE THANKS MUKESH "Roger Govier" wrote: Hi You needn't bother to copy the data to another sheet. Provided you have a header row on your sheet, mark the headers then DataFilterAutofilter Use the dropdown on the Field for Payment date, and select Blanks - this will show you all unpaid lines. Then use the dropdown on Customer name, select any Customer and you will see all his unpaid lines. If you want to see the totals, Insert a new row 1 and in the column where the amounts are held, say column F, enter =SUBTOTAL(9,$F$3:$F$1000) Change the range to suit, but this will give a total for all of the visible lines from your filter. -- Regards Roger Govier "mukesh" wrote in message ... hi, i have a spreadsheet that lists my various invoices for my export business. these include invoices that have been paid and invoices not paid. i then copy accross onto another spreadsheet (different tab, in the same workbook), the invoices that are unpaid. i wanted to break-down this 2nd spreadsheet client-wise, so that i know how much each clients owes me, instead of one large un-paid amount owed to me in total by every client of mine. i have read through yr help-menu and forums and understood and used the "SUMIF" formula and managed to get half a solution, where i can make a summary of the total of what each clients owes by BUT i can't get the individual invoices he owes me for. Also, my manner of doing such work is not productive as i have to manually copy data accross to my 2nd spreadsheet for un-paid invoices. any suggestion for a more productive solution ? or should i switch to access. i am synching this data with my PDA that runds MS OS and my pda has excel on it but no access. awaiting yr advise thanks mukesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sum total automatically in more than one sales invoices | Excel Worksheet Functions | |||
IS THERE A WAY TO TRACK INVOICES IN EXCEL AUTOMATICALLY? | Excel Discussion (Misc queries) | |||
Need help determining # of invoices | Excel Discussion (Misc queries) | |||
Pivot Tables - "simple" question | Excel Discussion (Misc queries) | |||
Subtracting paid hours from unpaid hours | Excel Worksheet Functions |