Originally Posted by
ArviLaanemets
I have made in Excel such file systems, when every user works with his own data. The set-up will be:
1. There is a Master Excel workbook, where:
a) The info from other workbooks is collected using ODBC queries (I have read info from every workbook into separate hidden sheet, and used UNION query to read the info from hidden sheets in Master workbook into summary sheet in same Master workbook, but probably you can use an UNION query to read data from other workbooks into summary sheet in Master workbook directly). The summary sheet in Master workbook is read-only;
b) Registries used in all other workbooks are maintained. Only the person maintaining Master workbooks enters info into those registries.
2. There is a number of child workbooks, with a certain user inserting only his info. Any information needed to use in several/all workbooks is readed from Master workbook (into hidden sheets using ODBC queries). User can enter this info into his table(s) using Data Validation List(s), or this info is read from hidden sheets by formulas.
All queries are set to be refreshed on open. The user can always refresh queries manually. N.B. ODBC query reads only saved info!
All workbooks must be on network resource which is available during work time. Avoid changing workbook's locations without dire need - you have to edit data connections every time you change workbook(s) location(s).
This set-up is more complex compared with shared workbooks, but all users can work at same time without interference form others. Lately the workbook sharing is made better, but some 10 years ago it was practically useless. And anyway the sharing prevents you from using some features, which is not the case with this set-up.