Dynamic reference to sharepoint files

%3CLINGO-SUB%20id%3D%22lingo-sub-2452758%22%20slang%3D%22en-US%22%3EDynamic%20reference%20to%20sharepoint%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2452758%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20file%20that%20summaries%20various%20other%20files%20(e.g.%20separate%20business%20cases)%20in%20one.%20I%20have%20already%20learned%20how%20to%20create%20dynamic%20references%20with%20the%20INDIRECT%20function.%20But%20this%20only%20works%20if%20I%20have%20all%20the%20source%20files%20opened.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20here's%20the%20challenge%3A%20All%20files%20are%20on%20a%20shared%20Sharepoint%20folder.%20I%20want%20my%20colleague%20to%20work%20with%20the%20file%20at%20the%20same%20time%20as%20I%20do.%20Thus%20any%20local%20references%20won't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuestion%3A%20How%20can%20I%20build%20a%20dynamic%20reference%20to%20the%20files%20in%20the%20Sharepoint%20folder%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Power%20Query%20is%20not%20an%20option%20since%20we%20have%20a%20custom-built%20xls%20format)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3ESamuel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2452758%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2455956%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20reference%20to%20sharepoint%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2455956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1080197%22%20target%3D%22_blank%22%3E%40samschuer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20file%20is%20on%20OneDrive%2FSharePoint%20site%20(and%20in%20synced%20folders%20locally)%20all%20links%20on%20other%20files%20are%20in%20form%20of%20%22%3CA%20href%3D%22https%3A%2F%2Fsomething%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsomething%3C%2FA%3E%22.%20You%20may%20check%20by%20cell(%22filename%22).%20That's%20to%20change%20the%20links%20or%20complicate%20formulas%20analyzing%20filepath%20starts%20from%20https%20or%20not%20and%20build%20formula%20based%20on%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20assume%20you%20are%20on%20desktop%20version.%20For%20Excel%20Online%20support%20of%20links%20on%20another%20files%20was%20introduces%20recently%2C%20but%20I'm%20not%20sure%20this%20functionality%20is%20GA.%20Don't%20know%20about%20Mac%20and%20mobile%20platforms.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2461460%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20reference%20to%20sharepoint%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2461460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthanks%20for%20taking%20the%20time%20to%20answer.%20Yes%2C%20I%20use%20Excel%20for%20desktop%20on%20Windows.%20Unfortunately%2C%20I%20haven't%20understood%20your%20answer.%20So%20I%20can%20obviously%20get%20the%20link%20to%20any%20sharepoint%20file%2C%20which%20has%20a%20weird%20code%20in%20it%20instead%20of%20a%20file%20name.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Flocation%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fexamplelocation%2FEH7haYausdhHa....%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20I%20don't%20understand%20is%20how%20to%20create%20a%20formula%20with%20that%20which%20references%20to%20the%20cells%20I%20need.%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20the%20INDIRECT%20formula%20the%20logic%20is%20known%20as%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22sy1%22%3E%3D%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-indirect-function%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%3CSPAN%20class%3D%22kw4%22%3EINDIRECT%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20class%3D%22sy0%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22st0%22%3E%22'%5B%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22sy1%22%3E%26amp%3B%3C%2FSPAN%3Eworkbook%3CSPAN%20class%3D%22sy1%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22st0%22%3E%22%5D%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22sy1%22%3E%26amp%3B%3C%2FSPAN%3Esheet%3CSPAN%20class%3D%22sy1%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22st0%22%3E%22'!%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22sy1%22%3E%26amp%3B%3C%2FSPAN%3Eref%3CSPAN%20class%3D%22sy0%22%3E)%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3EIt%20doesn't%20work%20though%20to%20replace%20the%20workbook%20part%20with%20the%20link%20above.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies

@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.

@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.


@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

image.png

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

image.png