Data Connections (Multiple Workbooks) to Existing Workbook

I have 6 workbooks that share common tables to some extent. In an effort to make my life easier I've tried to set up an excel workbook that would act as a data source. I then set up connections to the source data workbook. This works fine until you try to open more than one workbook and it errors out stating that it can't update the tables because they are in use. My first thought was to share the source data workbook but this did not help.

I have to update these tables every morning using a sumifs or vlookup to other workbooks to extract the data. I then paste as values. With that in mind I'd rather not use access.

Does anybody have any ideas? Any thoughts or help would be appreciated.

**** bump ****

For those interested -

The desired results can be accomplished by setting up an ODBC connection to a workbook.