Forum Discussion
Dynamic reference to sharepoint files
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.
SergeiBaklan 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://location/
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:=https://exceljet.net/excel-functions/excel-indirect-function("'["&workbook&"]"&sheet&"'!"&ref)
It doesn't work though to replace the workbook part with the link above.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
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.
