Seeking a Better Solution for Realtime Data Linking from Multiple Sources in Excel

We are currently facing some challenges while trying to link data from 8 different data entry sources into a single master data file in Excel. Our current approach involves using the XLOOKUP formula, but we've encountered several issues:

1. The master data file needs to be closed and reopened to obtain real-time updates from the 8 data entry sources. This process is inconvenient and disrupts our workflow. Is there a way to ensure that the master data updates automatically without reopening the file?

2. We occasionally encounter crashes and see the #REF error symbol in our data. We suspect this might be related to some of the data entry sources closing and reopening their files. Is there a way to prevent these errors or identify their root cause?

3. Adjusting the master data can lead to errors because we've linked it to several columns in every row. If we need to adjust just one column, the other 6 columns become problematic. Is there a method to make adjustments more seamlessly without causing errors in the linked data?

We'd greatly appreciate any insights or alternative approaches the Excel community might have to help us achieve real-time data linking from various sources, even when those sources are on different computers. Thank you in advance for your assistance!