Retrieve table data from a SharePoint Excel workbook into another SharePoint Excel workbook

[CapRavOr]

Hello,

The issue: At my company, we utilize SharePoint co-authored Excel workbooks to retrieve, store, and manipulate data. Aside from that being problematic in and of itself, users want to see populated data in one workbook based on data from another workbook. Essentially, a formula looks like this when the source workbook is 'Open',

=IFERROR(XLOOKUP([@[Batch Number]],'Data Here.xlsx'!data_table[Batch Number],'Data Here.xlsx'!data_table[Important Date]),"").

When the source workbook is 'Closed', formulas look like this,

=IFERROR(XLOOKUP([@[Batch Number]],'https://company.sharepoint.com/sites/Production/Shared Documents/Data Documents/Data Here.xlsx'!data_table[Batch Number],'https://company.sharepoint.com/sites/Production/Shared Documents/Data Documents/Data Here.xlsx'!scorecard_data_ff[Important Date]),"")

So, when the source workbook is closed, any data using the link/reference,

https://company.sharepoint.com/sites/Production/Shared Documents/Data Documents/Data Here.xlsx'!data_table[Column]

Comes up as a #REF error. Which makes sense and doesn't. And when I go to Data -> Edit Links, I can't find a way to make the data available while the source document is closed. Is this even possible? Is there a better way to get data from an Excel workbook housed in SharePoint so that it can be used while the workbook is closed that I'm not seeing? Many people use the data source spreadsheet and update it daily. Executives are the ones trying to evaluate all of the data. I am working on a Data Model that can be utilized by the whole company as well as trying to convert a lot of these spreadsheets to Power BI. So there's a horizon.

Any help is appreciated!!! Thanks in Advance!!