I have a project which will have approximately 20 users preparing worksheets which will contain the same format but which will have information stored on separate lines amongst the 20 worksheets. I need to take these 20 worksheets and merge the information that is entered into one Main worksheet formatted in the same manner.
First question: Can I create a macro to open these 20 individual spreadsheets (if they even have to be opened to copy).
Second: How would I write a formula to pull the information from each filled cell and merge it onto the matching cell on the main spreadsheet.
The Main Spreadsheet will have 10 columns and 1000 lines. Columns A through E will be prefilled with policy information.
Individual users will open a copy of this Main Spreadsheet, fill in Columns F through J for the policies that affect them, and then saving the Main Spreadsheet with a unique name for them (ie; User 1, User 2, etc).
At the end of the day I need to take the 20 documents from various users and combine their information onto the Main Spreadsheet.
I'd appreciate any help anyone can provide for a quick way to handle this process.