Forum Discussion

SvenM1005's avatar
SvenM1005
Copper Contributor
Feb 04, 2021

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

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

excel
Excel for web
Formulas and Functions

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 04, 2021

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

    • SvenM1005's avatar
      SvenM1005
      Copper Contributor
      Feb 05, 2021

      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.   

Resources