Summary Sheet For Drawings Log with Multiple revisons

Hi,

My problem is quite simple but I can't quite find a workaround.
I have to maintain several logs for drawings of various disciplines.The summary for these drawings have to be provided two times a week.
I have attached an extract of the sample log sheet.

My problem is that I cannot create a summary sheet to show all the required information such as Total No.submitted,Hw many with A /B/C status,How many have been received back with status,How many are still not received etc. I have an issue with the revisions for the drawings;the latest revision status has to be counted for a drawing i.e if rev 3 of a drawing is A status,that should be counted.This also creates a problem for me when counting the total no of drawings; I need only the latest revision to be counted for a specific drawing number.

Another problem is that the list gets updated daily;so have to create a dynamic range so that I dont have to update the formulas every now and then.

Also it would be helpful if anyone can guide to use pivot tables for this issue.



Thanks in advance.