Thread: How do I list invoices marked not paid on another sheet

On Sheet1(Labled Income) I have invoices set-up as follows:

Colums:
A Dates B Invoice#s C Amounts D Paid-Y/N E Customer Names

On Sheet3 (Labled Oustanding Customers) I want to list all invoices from "Income" that are marked "no" on the D Colum. can anyone help me out please!

For example if my Invoiced read as follows on Sheet1 (Labled Income):

Date Invoice# Amount Paid-Y/N Customer Name
3/22/2011 1000 30.00 yes aaa
3/23/2011 1001 50.00 no bbb

I want to list the following on Sheet3 (Labled Outstanding Costumers:

3/23/2011 1001 50.00 bbb
Please Help!

This can be done with a macro, or with a lot of not-too-hard formulas. Do you have a preference?

With VBA you would be able to do anything you wanted on the "report" sheet because each time you you "updated" the report it would paste in clean fresh values.

With formulas you really wouldn't be able to do anything on the report except read it since all the cells would have formulas bringing over the values from the main sheet in real time.

Post up your sample workbook and the answer the question above and we can offer you some help.

Originally Posted by JBeaucaire

This can be done with a macro, or with a lot of not-too-hard formulas. Do you have a preference?

With VBA you would be able to do anything you wanted on the "report" sheet because each time you you "updated" the report it would paste in clean fresh values.

With formulas you really wouldn't be able to do anything on the report except read it since all the cells would have formulas bringing over the values from the main sheet in real time.

Post up your sample workbook and the answer the question above and we can offer you some help.

Attatched, Please find my sample workbook.
Regarding your question; No preferance, whatever you think is best!

I inted to have a diferent workbook for each year, in this format will I be able to copy the workbook and its formulas without the data to start a new for the next year?

The only Sheets I started placing data in is the Income, and the Income report for programing formulas; In the Outstanding Accounts sheet I started placing colums but if I need to take these out let me know.

You don't need all those dates that aren't used. All the data should be maintained on one worksheet, then use PivotTables to summarise the data

You need to read up on PivotTables, a good idea would be the List feature in Excel 2003

This is just a very quick example, I have used the PivotTable Group feature to group your data by Months

Originally Posted by royUK

You don't need all those dates that aren't used. All the data should be maintained on one worksheet, then use PivotTables to summarise the data

You need to read up on PivotTables, a good idea would be the List feature in Excel 2003

This is just a very quick example, I have used the PivotTable Group feature to group your data by Months

Thank you for your exanple of pivot table Nice!
what I need thow is not yes or no sums grouped my month, but a list of just the ones marked no, listing Date, Invoice#, Amount, and Customer Name, so I know who din't pay.

I have the version of Excel that came with office 2000. I tried to make pivot report but says colums must have headings???

(P.S.) I had all those dates because I was testing my formulas.

Just experiment with the PivotTable until you get the results that you want

Attach what you have & explain what you need changing