Generating reports using macro

**cvsuryam** · 05-01-2011, 12:19 PM

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.
which are located at:
http://wikisend.com/download/394540/sample data.zip or goto
sample data.zip

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 using macro or some other way(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
Suryam

**mrice** · 05-01-2011, 02:05 PM

I would suggest that you consider using named ranges in your basefile and then refer to these in your other workbooks. If you change the range to which the name refers to in the basefile, there will be no need to update the references in the other books.

You can even set up dynamic named ranges which will automatically adjust to the size of continuous blocks of data. have a look at http://www.ozgrid.com/Excel/DynamicRanges.htm

Hope this helps.

**cvsuryam** · 05-02-2011, 02:25 PM

hi Mrice , thanks for your speedy answer...I tried in this way...something is missing in my mind.
could you please do this work for me on my sample data so that I can use the same for the rest of the sheets. you see I am new to use this...if possible please spare some time to do my work..thanks bye Mr.Suryam

**cvsuryam** · 05-02-2011, 02:29 PM

hi Mrice , thanks for your speedy answer...I tried in this way...something is missing in my mind.
could you please do this work (I mean show me a way )for me on my sample data so that I can use the same for the rest of the sheets. you see I am new to use this...if possible please spare some time to do my work..thanks bye Mr.Suryam

**cvsuryam** · 05-02-2011, 02:46 PM

Is there any other " ready made tool" or method available to meet my requirement? This is little urgent as i have to give reports by 5th of every month. Pls suggest, thanks suryam

**mrice** · 05-02-2011, 03:09 PM

OK.

In PRODUCTFILE.xlsx in I11 create the name Branch1Prod1Acheived

In BRANCHFILE.xlsx in E10, use the formula =PRODUCTFILE.xlsx!Branch1Prod1Acheived (this can be done by hitting = and then navigating to the named range.)

The reference should work independently of any move of the position of the named range within the sheet.

Hope this helps.