Extract data based on month/year to a simple table.

Hello all, any help on this would be very much appreciated.
I have a spread sheet with a large amount of information in it. I have attached a sheet which shows a couple of simple tables and a section of cells copied from the spreadsheet with other cells hidden.

I use this information to produce a simple report every month. To do this I filter the columns and manually copy the data. This is a little time consuming so I would like to create a better way to do this. At the same time I would like to show the data in a table and a chart.

The idea is to add a second tab to the workbook that will extract the required data based on the date entered.
This is simply collecting the required data for the previous month. So when I hit the first of every month I will generate a report for the previous month.
I would like to have a table and chart on the page which I can simply email to other team members.


The only cells required for reporting are Customer, Job type, Drawing Received, Engineer and BOM complete.
Customer can be one of many customers
Job Type is ECN or RFQ
Drawing received is always a date
Engineer is one of the following RMB, IM, RP or SB
Bom Complete is always a date

So for the table I require something along the lines of this (see attachment)
With the data (count) being added to the places with 0.

Any Job Type of ECN, a received date of January 2014 and an Engineer of Russ will count for the RMB ECN Received cell. Similarly for the other engineers and options.

Also required is a table which lists the same data but by the customer (see attachment).
For this table the same data is required but only based on date, customer and job type.
For this table I can have ALL of the possible customers listed in the table (I have only listed 4 here) and simply delete the rows with no data.
So for customer = Misc count the ECN received for the given month and year. Then the same for each of the other columns such as ECN complete Etc.
Once I get this far I will also look at creating a pie or other chart from the data.

I know this may be a lot to ask but if anyone can help with any part I would appreciate it.
I can obviously copy and paste formulas and change cell references once I have one part working.
So probably only need a couple of formula’s, one for each table?

many thanks

Can you upload a sample spreadsheet with a bit more data?

Hi, nearly have it cracked,well most of it. I have posted at the following thread and have it working nicely apart from the third part to check the date. If you can help with this I would appreciate it. http://www.excelforum.com/excel-form...d-formula.html

thanks