Generating reports using macro in excel2003

Hello,
Hello
I have a task to be done once on every month-end. I am working in a private bank. I have a file called BaseFile. I need to generate report files "ProductFile" and "BranchFile".WE ARE USING MS EXCEL 2003 ONLY.

let me describe them briefly
1. BaseFile.xls -- contains the data which I have to enter manually after getting relavent info from other sources.

2. ProductFile.xls --it will be generated from the BaseFile.xls. It contains details of each product---branchwise info of both the total amount value and Number of transactions.
there are about 30 products and 25 branches sofar. these may increase in very near future.
To generate this file , I am using the "vlookup" on month in the BaseFile .

3. BranchFile.xls--- it will be generated from the ProductFile.xls.
(Note: I have followed this way...you can get it from the BaseFile also)It contains the details of each branch--productwise info of both the total amount value and Number of transactions. To generate this file, I am using " cell reference" formulas based on ProductFile.(basing on the suitability you may get it also from BaseFile)

you can refer my sample files which are attached.
In this I have given the details for 5 branches in the BaseFile and only one product and one branch respectively in ProductFile and BranchFile....there will be 30 worksheets like this for 30 branches in ProductFiles and 25 worksheets in BranchFile.

My difficulties/problems are these:
1. when I need to enter a new branch in the Basefile,in between, then all the the "vlookup formulas" of the productFile are disturbed and consequently the ProductFile and BranchFile are also getting affected in the formulas...and I am facing a hectic problem of going to each cell and to change the vlookup formulas which are below the newly added branch. to avoid this I am adding the new branch details at the end of the existing details in the BaseFile which normally should not happen as the branches must be arranged statewise and locationwise.

2.when I need to enter a new branch in product file,in between, then all the the "cell reference formulas" of the BranchFile are disturbed...and I am facing a hectic problem of going to each cell and to change the "cell reference" formulas. I cannot add the branch at the last row because the branches must be arranged statewise and locationwise in my final report.

My questions are:
Is there any automatic or simplified way to do this task (to generate the ProductFile and BranchFile based on BaseFile) ? Remember that we are using MS Excel 2003 only.
Please help me in this issue. Your help is highly appreciated..

thanks bye suryam