Hi,
  I need help on excel.I need to generate a summary report which will help me to keep track on the no. of items that had been closed/pending open for the particular month.The summary report will be generated on a monthly basis & it will be saved in the common folder where all the input files are found.

I need to retrieve data from various workbooks,in order to generate the summary report.Each workbook contains >1 worksheets.Moreover,all the workbooks are stored in a common folder.

The Input file for each workbook,each worksheet will have the following common fields:-
Action_source
Status_Item
Status_Item_dtls
Action_Due_date
Action_Party
Action_Party_Email
Classification_of_Status

How do i merge/link all the data from the various worksheets(from the various workbooks)into 1 sheet? Based on the merged/linked data,how do i generate the summary report?

The summary report generated will gives an overview summary on the following:-
a)No. of outstanding activities with Classification of status=High
b)No. of outstanding activities with Classification of status=Medium
c)No. of outstanding activities with Classification of status=Low

-if Action_Status=Open,need to based on Action_Due_Date to check when the item is due,then, based on the Action_Party_email_address to auto send out the email to the respective Action_party

For example, now is April & the job is ran to generate the summary report in April.
If 'Action_Due_Date'=' May-07', a reminder email will be auto send to the respective 'Action_Party' based on the 'Action_Party_email_address'.

Thanks a million inadvanced.