Linking Sharepoint hosted excel files using an indirect formula

[lewdow]

I am trying to link one cell from a sharepoint hosted excel file into another sharepoint hosted excel file so that when the date changes in file x, it updates using the cell link in file y.

I have done this so far using:
='https://share.point.com/sites/site/subsite/Team%20Documents/folder/subfolder/[file%20y.xlsx]tab'!$E$6

however whenever I close the file and check it in, the formula reverts to using a local sharepoint drafts folder file path and being as multiple people will be using this is no use as they don't have access to my local drafts.

In order to get around this I have tried baking this into an indirect formula, with the subfolder and [file%20y.xlsx] being cell references as these can be variable. I have tested the logic multiple times and in theory it should work fine but it just returns #REF!.

Even taking out the variable element and putting:

=indirect("'https://share.point.com/sites/site/subsite/Team%20Documents/folder/subfolder/[file%20y.xlsx]tab'!$E$6")

returns #REF! - what is it about the combination of an indirect function and linking to a sharepoint hosted excel file that it doesn't like?

Thanks

Lewis

 

[terrysmith22]

Hi, did you ever get an answer to this? I am trying the exact same thing