Multiple open Excel files with same source file issue

[Bonta]

Hi Guys,

In the last few years I learned a ton of stuff about Excel that I use daily at work.
Now I mostly work in HR and we have Excel mutation forms that fill in some staff info automatically in some fields based on their staff number.
The source for this is a regularly updated central data dump file.
The data dump file and the mutation form is on the network L drive.
Now I applied the same idea to a Word document by embedding an Excel object that uses to same data dump file to fill in the names, addresses, date of birth etc in the header of each letter. This document is on my desktop (network H drive) but I've also tested this with it being on the same L drive as the files above.

Now here's the problem.
If I have the Excel form open and I then proceed to open up the Excel object in Word, the data in the form jumps.
About 5 out of 9 referencing formulae now display data from the wrong line in the data dump file.
It always does this and it always does it from the same wrong line.
Although the fault is always the same (say, I require line 755 and it always gets line 896) I cannot find any logic in it.
It's not like it always gets data from 141 lines down, sometimes it's 1300 lines, sometimes 60 etc.
I also can't find any reason that it may be related somehow to the difference in staff number.

Can anyone please explain to me why this happens and how it might be prevented?
I thought I had fixed it by converting the Excel object in word to an OpenDocument sheet.
Today I found it would still do the same as above but no longer always.

FYI, I have tried the formulae referencing the data dump with both vlookup and index&match but the problem is identical.

I really hope someone can at least explain this to me, if not find a fix for it.

Sorry for the long read.
Here's an Excel potato (9gag joke)

 

[Bonta]

I forgot to add;
Sadly at work I'm forced to work with Windows 7 and Office 2010.

 

[Bonta]

I have to say, it's a little disheartening to see 20 views and 0 replies knowing that I'm already down to page #9 in the list of questions.
Guess nobody has a clue why this happens either.

 

[SpillerBD]

Now here's the problem.
If I have the Excel form open and I then proceed to open up the Excel object in Word, the data in the form jumps.
About 5 out of 9 referencing formulae now display data from the wrong line in the data dump file.
It always does this and it always does it from the same wrong line.

An Excel object in Word... Such an object could display any part of a much bigger sheet.
Linked objects often have enough caveats to keep most people from using them, so I'm not surprised no one else has replied.
Also what you describe is difficult to understand.
However, are you saving the Excel file before opening the Word file?

 

[Bonta]

An Excel object in Word... Such an object could display any part of a much bigger sheet.
Linked objects often have enough caveats to keep most people from using them, so I'm not surprised no one else has replied.
Also what you describe is difficult to understand.
However, are you saving the Excel file before opening the Word file?

It's not a linked object, it's embedded in the docx.
And yes this happens regardless of the excel form being saved or not.