How can I stop Excel from updating links from a Worksheet to a Remote File?

[CSWillow]

I have a number of Excel files for individual cricket players that are linked to a main summary file.
e.g. files for Tom Smith, Matt Brown, etc. are linked to the main file Club Opponents.

I have linked the Smith, Brown files to the main file as Worksheets.
When I open the main file, the link reverts to a remote file.
I need the links to stay as Worksheets.

Would appreciate any help to correct this problem.

[AliGW]

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[CSWillow]

AliGW:

I have attached the 2 relevant sample Excel files. The actual files are much, much larger but I have scaled them down to a manageable size whilst retaining the relevant facts.
On the actual files there are about 30 player files that are linked to the main file (Opponents) and there are about 30 opponents that the player files are linked to.

I do get the same intended outcome on the Opponents file whether the link is from a Worksheet or a remote file.
Is it just that it shows 'Worksheet' when the player file is open and 'Remote file' when it is not open?

However, there are times when I go to "Edit links to files" and it shows 'Worksheet' for all 30 players whether or not their files are open?

You may also be able to help me with an associated problem on these same files.
In a player's file, there are occasions where the player is playing against a new opponent for the first time.
This necessitates a new row being inserted in his Totals tab. This means that 'Bayswater-Postels' moves from row 5 to row 6.
When this occurs, the link in the Opponents file automatically changes from row 5 to row 6, provided the Opponents file is open at the same time.
If the Opponents file is not open, the link remains at row 5 and this throws all the other links against the other opponents out by one row.
I then have to re-install all the links for all the opponents with the correct row.

I believe there is a macro which can automatically open the Opponents file when this occurs.
Can you also help me with this problem and suggest the wording for the macro.

I hope I have explained this so you can understand what I mean.

Any help would be greatly appreciated.

[AliGW]

I believe there is a macro which can automatically open the Opponents file when this occurs.
Can you also help me with this problem and suggest the wording for the macro.

I'm afraid I can't help you with any of this particular issue, but somebody else will. This second query is different and needs to be posted in its own thread in the VBA and programming section of the forum. Thanks.