Create a link between two Excel files

Copper Contributor



I am currently working with several Excel files to process payments to volunteers in our company. 
A colleague who doesn't work in my company anymore has created two files which are linked to each other. The idea is that I download volunteer reports (containing information like name & address & IBAN) weekly which I then copy into one of the files (data_volunteers) using a copydata macro. 
This file is then connected to a template file which automatically fills out the needed information when I type in the volunteer number (this data is collected from the data_volunteers file).
Somewhere in this whole process, something is going wrong because data is not copied anymore to the template file. I was wondering if anyone might know how this was even made and how I could fix this or could start all over again in an easier way. I am not an insane Excel geek, can work out the macros a tiny bit but that's it.
Would appreciate any help/tips! Thanks in advance!

6 Replies



Trying to figure out how to resolve your situation with only a verbal description is well nigh impossible. I know your actual files contain real names and other identifiable information so they can't be shared. However, you'd be helping us help you if you could create a mockup of the real spreadsheets, using false names, and post those on OneDrive or GoogleDrive with a link pasted here that grants access.


Also let us know what version of Excel you're working with, or if you have a Microsoft 365 subscription. There are newer capabilities in that subscription or in the newest version of Excel, capabilities that might enable you to achieve the desired result without a macro. But, as noted, all of that would be easier to determine if you could post mockup samples of the actual workbooks.

We have an Office 365 subscription I believe



Wow! That may be more than I can handle, but perhaps somebody else in this forum will be able to take a look at those two sheets.


In examining them quickly--unfortunately I have a major engagement today that will preclude spending more time--I did notice that the formulas in the "template declaraties automatics" spreadsheet are looking for data in "data_volunteers.xlsm" but the sheet I downloaded is "data_volunteers1.xlsm"  If that's also what you're working with then that could explain why no data is being found: it's looking for a data sheet by a different name. So check that name and change it, if that's the case.


Beyond that, I need to invite others to take a look for now.

hey! Thanks for your quick response. I just created a new file based on the data_volunteers one so I could delete the data and upload to OneDrive. The file is actually called data_volunteers when I'm working with it so that's not the problem!


I've taken a few minutes to write a different formula altogether that can pull the data without a macro.

The basic formula is this FILTER formula. You'll need to adjust the first references to the appropriate column in the data_volunteers sheet. This formula retrieves the Address data, from column L in the source file.  I adjusted it in a few other columns just to verify its efficacy. Did not copy to all of them, so some of the original formulas remain.



I'm leaving home now for the rest of the working won't be able to answer any questions for the next 8 hours or so.