I need to create an Excel Template that will be stored as xml.

The template will consist of four worksheets:
Master Data
Department A
Department B
Department C

A third party program (over which I have no control) will open the workbook
and dump company data into the Master Data worksheet.
I need to somehow populate each of the Department worksheets with only the
data relevant to that particular department.

The Master Data worksheet will have the following columns
Dept Code:
Transaction No:
Transaction Type:
Date:
Amount:

I need to somehow get any rows that have a Dept Code of A into the
Department A worksheet, rows with a Dept Code of B into the Department B
worksheet etc.

I cannot have any macros as this document needs to be stored as xml (this is
the client's pre requisite).
There cannot be any blank lines in the middle of the Department A,
Department B, Department C worksheets.
There must be no user intervention required.

I had thought of populating the first 1000 rows of so of each of the
department worksheets with a formula to display the corresponding rows of
Master Data if the Dept Code equalled A but this would leave blank rows
everywhere.

Can it be done? Any ideas?

Thanks
Tony