Dynamic reference to sharepoint files

[samschuer]

Hi,

I want to create a file that summaries various other files (e.g. separate business cases) in one. I have already learned how to create dynamic references with the INDIRECT function. But this only works if I have all the source files opened.

But here's the challenge: All files are on a shared Sharepoint folder. I want my colleague to work with the file at the same time as I do. Thus any local references won't work.

Question: How can I build a dynamic reference to the files in the Sharepoint folder?

(Power Query is not an option since we have a custom-built xls format)

Kind regards,

Samuel

[#2]

@samschuer 

If file is on OneDrive/SharePoint site (and in synced folders locally) all links on other files are in form of "https://something". You may check by cell("filename"). That's to change the links or complicate formulas analyzing filepath starts from https or not and build formula based on it.

I assume you are on desktop version. For Excel Online support of links on another files was introduces recently, but I'm not sure this functionality is GA. Don't know about Mac and mobile platforms.

[#3]

@Sergei Baklan thanks for taking the time to answer. Yes, I use Excel for desktop on Windows. Unfortunately, I haven't understood your answer. So I can obviously get the link to any sharepoint file, which has a weird code in it instead of a file name. 

https://examplelocation/EH7haYausdhHa....

What I don't understand is how to create a formula with that which references to the cells I need.

With the INDIRECT formula the logic is known as:

=INDIRECT("'["&workbook&"]"&sheet&"'!"&ref)

It doesn't work though to replace the workbook part with the link above.

[#4]

@samschuer 

INDIRECT() requires that both files shall be opened, thus actually you don't need full path, that's only if parse CELL("filename").

Formula will be the same. Here if both are opened

and if targeted file is closed only link without INDIRECT() works

[#5]

I was also fighting with the strange path you describe when I click 'copy link' from the sharepoint site, but I found that if I have the folder synced to my computer, I can reference the cell while the file is open and then when I close the referenced file, my formula automatically changes to https://******.sharepoint.com/Shared Documents/LocateImportData.xlsx'!DataImport[@[Date of Request]]

Although I am still struggling with doing a look-up without opening the reference file as well.

[#6]

For anyone finding this, I found a solution that works in my case. Experimenting with Sergie work above I found that this format will work:

='https://*****.sharepoint.com/Shared Documents/[LocateImportData.xlsx]Import Data'!B2

I found that I have to use a sheet and cell reference and not a table reference.  I hope this helps someone else and they will find it before losing sanity searching like I almost did.

[#7]

I have the same issue I came to the same conclusion, after loosing days trying to get past this issue. However this is not a solution, I cannot believe that I cannot reference a TABLE without the update issue.