Forum Discussion
Dynamic reference to sharepoint files
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.
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.- 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.
