I need to share certain membership data with a number of team managers who run hockey junior teams for a club. They cannot have access to the main membership details which are in a password protected excel file. So i have created a new sheet that links to the data they need and it works fine but for two problems...

1. Every time I open my Junior Managers file it needs to update the data from the main file but this means putting the password in and I can't share that with others. So I need a way to encode the password into a macro or something so when the sheet is opened it updates automatically. My Junior sheet is also protected by a password.

2. Every time the update happens all my formatting vanishes in the destination file so the sheet is a mess and I have to reformat. The file is protected so that I can hid the formulas as I don't want anyone to see where the data comes from. I tried to write a macro to reformat the sheet each time it updates BUT it fails because you can't run a macro on a protected sheet, you have to un-protect first which means I'd have to share my files password and then people can fiddle and see the formulas.

This is the formula to fetch the data...
=IF('C:\Users\myname\Dropbox\##### Hockey Club\[Membership Database 2014.xls]Membership Details'!$J22<15,'C:\Users\myname\Dropbox\##### Hockey Club\[Membership Database 2014.xls]Membership Details'!A22)
it is just pasted into each cell in the sheet so maps every cell across, then i dleted the columns others must not see for data protection reasons. The <15 section selects only the junior under 16 records.



Is there an alternate and better way to fetch the data?
Is there a way to get round the password issue, eg not being able to share the membership password with others?

All suggestions welcome