Consolidating data from different users workbooks

[Nadir]

Need a solution in following 2 Cases:
1. I have 5 analysts who uses an excel MIS format provided by me for each, the sheet contains the headers with different headings. Major areas where i am stuck is that i had to ask them each time, collect their sheet extract the changed data and populate the same into one Master MIS on same format.
2. Have number of field offices from where i use to received the data on questionnaire format. the same format which is uses by the analysts and eventually came to me to populate the identicle items.

I want to automate this process as the field consist of 1400 locations who uses the MS. Outlook to send this Excel based questionnaire and the automation from my analysts sheets to my master sheet.

The Format is looked like the

 

[Somendra Misra]

Hi Nadir,

Welcome to chandoo.org forum.

If you are comfortable with VBA than you can get a solution at below links which give number of options for doing this task.

http://msdn.microsoft.com/en-us/library/cc837974(v=office.12).aspx

Have a look at it.

Regards,

 

[Nadir]

Hi Nadir,

Welcome to chandoo.org forum.

If you are comfortable with VBA than you can get a solution at below links which give number of options for doing this task.

http://msdn.microsoft.com/en-us/library/cc837974(v=office.12).aspx

Have a look at it.

Regards,

Dear Thanks i had gone through the same & trying the same at my level. need a solution for automating the same in real time. Have the excel or Access have any functionality where i can design the same format in Forms templates and request to respond on subject form & when the form returns to me then my centralized MIS is updated on real time.

If Excel is unable to handle the same, can the access do the job for collecting the data automation, i will then convert the database to excel format & update the changes only into my excel Master File.

Thanks n Regards,

Nadir.

 

[vijay.vizzu]

Dear Nadir...

One question is that... Are you getting sheets from your analysts as an attachments ?? As per my idea... you have two options
1. If you have workbooks, which has been sent by your analyst, and you have the master file and you want to add the details from that workbooks to in your master file. It can be achieved by VBA. By looping through the all the workbooks, and copy the data from file and paste it in your master file...

2.If you want, a form should be sent by your analyst through outlook and whenever the form received, the master file should be updated. It can be achieved by VBA.

So, which option you choose. Opt1 is little bit easy (i tried) but Opt2 is little bit complex becoz not yet used by me..

So, no problem.. In this forum, so many excel experts are there, they will try to resolve the issue in short a time