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

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
Data>Filter>Autofilter
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" <[email protected]> wrote in message
news:[email protected]...
> 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

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
> Data>Filter>Autofilter
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>