lavi0007 wrote:but don't you think for less technical person like my other office staff, its kind of complicated to explain them why they have to do it.
No, it is easy to explain: Snapshots of the source file data are embedded in the target file. You can carry the file on USB or upload the target file and it will keep on working without the source files unless you try to refresh.
The manual update will try to read the source files as they are stored on the disk
. It will re-import the data cache from the stored source file rather than the dirty version in memory.
If you need spreadsheet links that work differently, you may try the DDE function. It loads the source file invisibly at first and it can update automatically when you make the source file visible and edit.
Please mind: A spreadsheet is not a database. For what you obviously try to do and for many reasons, the spreadsheet is the wrong tool. A database is designed to work with many millions of rows in hundreds of interrelated tables without problems. No technical person would ever build up a database structure in a spreadsheet (well, may be some tiny collection of small lists without too much importance). For less technical persons a well crafted database setup is far easier to work with and less error prone.