Hi everyone,
I have attached 4 sheets to exemplify my query - Example Master, Example Template 1, Example Template 2 and Example Template 3.
I am going to receive many template files from different people and am looking for suggestions on how I can update a master file from the source data as and when it arrives, without having to copy and paste each time.
Any assistance will be greatly appreciated.
Cheers, Avendi
Would the templates be found in a specific folder, or would they arrive as email attachments?
Acts 4:12
Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.
Hi natefarm,
Arrive as emails but will be put into specific folders.
Cheers, AV
Create another sheet in the Master workbook called Templates. A1 = "Folder", B1 = "Template" for headings. Enter your folder and heading names beneath. If all the templates will be in the same folder, then you can just enter it into B2 and adjust the code to use B2 for all folders.
Add the code below to the Templates sheet, and if you want, add a "Get Template Data" button to run the code, or you can just assign it to a shortcut key.
Please Login or Register to view this content.
Here is a formula based solution that would require that all workbooks retain theirs names and be open at the same time in order for this to work.
I inserted a column at Column A of all the workbooks. In the sub workbooks there is a formula that numbers the rows that have data. This is the formula entered into A2 of each sub workbook:
Formula:
Please Login or Register to view this content.
In A1 of each sub workbook except the first this formula to retrieve the max value from column A of the previous workbook is used using the name of the previous workbook in the series :
Formula:Please Login or Register to view this content.
All of the max values in the workbooks is brought forward to the Master workbook with this formula. The names of the workbooks for this example are in BN3:BN5 and this formula is entered in BO3 and filled down:
Formula:Please Login or Register to view this content.
The above values are then used in column A of the Master workbook to determine how many rows to assign to each workbook. This formula is entered into A2 and filled down:
Formula:Please Login or Register to view this content.
Then to retrieve the data from each workbook the following VLOOKUP formula is entered into B2 and filled across and down:
Formula:Please Login or Register to view this content.
Here are your workbooks setup so that you can try out the setup.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Trouble uploading workbooks. I'm trying again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks