Create a link between two Excel files

Copper Contributor

Hello!

 

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

@NickyLotte 

 

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

@NickyLotte 

 

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!

@NickyLotte 

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.

=FILTER([data_volunteers.xlsm]Sheet1!L$2:L$18,[data_volunteers.xlsm]Sheet1!$B$2:$B$18=$B2)

 

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