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:

='company.sharepoint.com/sites/customer/Shared Documents/General/[Test 2.xlsx]Sheet1'!$A$2+'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