Stop SharePoint Online hosted linked Excel file displaying absolute path in formulas

[SvenM1005]

Hi All

I have a series of linked Excel workbooks hosted in SharePoint Online - Test 1 and Test 2 for the purposes of the below.

If I have both the files open, the formula's in Test 1 (target) are displayed using the relative path as follows:

='[Test 2.xlsx]Sheet1'!$A$2+'[Test 2.xlsx]Sheet1'!$A$6

However, if I close Test 2 (source) the formulas in Test 1 display the absolute path as follows:

='https:// company.sharepoint.com/sites/customer/Shared Documents/General/[Test 2.xlsx]Sheet1'!$A$2+'https:// company.sharepoint.com/sites/customer/Shared Documents/General/[Test 2.xlsx]Sheet1'!$A$6 

This rapidly becomes unworkable when i have formula's pulling data from a number of different workbooks.

Does anyone know how i can get the target spreadsheet to always display (or toggle on/off) the relative path?

Thanks

Steve

[#2]

@SvenM1005 is it ok/acceptable that the cell only updates when the other sheet is open?  If so you can use INDIRECT() function.

[#3]

@mtarler thanks for the reply. Not really I'm afraid - these are a series of linked financial data sets so having to open them all to update would risk the data potentially being wrong (not updated but taken on face value). Additionally if all the sheets were open, the issue is negated anyway as it shows relative links (or just workbook name) if the source sheet is open.