Create ODBC queries to get tables from specified workbook into specified sheet {Source tables must be properly designed - must have a single header row with headers for every column, preferably don't have gaps row- or columnwise, all columns must have proper data (i.e. not so that e.g. in Producer column you have "Ford", and then the column is empty until next car Producer is entered), etc.}. Set queries to be refreshed when the Master workbook is opened.

Query reads saved data from source workbook. When you opened the Master workbook, and some user edits source workbook afterwards, you can't get edited data until the other user doesn't save his/her workbook. But this is so with all other ways too - you can access other workbooks saved somewhere. When user opens workbook, Excel creates a virtual copy of it, and user makes all changes in this virtual copy until the workbook is saved. (Of course you can set autosave on for source workbooks.)

When you set the query to be refreshed on open, as I adviced, the master workbook gets latest saved data from source workbooks. When you have the master workbook open for longer time, you can always refresh all queries (or specific query) at will. And you can additionally set every query separately to be refreshed at wanted time interval - but this may be quite annoying when suddenly your workbook freezes when you are halfway in entering something.

Queries may work in other way too. You can have some general tables on Master workbook, which you keep up to date, and other users can read those tables into their workbooks, where thy may be used e.g. as sources for data validation lists, or user workbook has some formulas to get additional info into his/her table. The main rule is - data are entered only in one workbook and all other workbooks can only read this data. I myself usually hide sheets with queried data from user to avoid something messed up accidently.