References to different files via formulas stopped working

%3CLINGO-SUB%20id%3D%22lingo-sub-2081857%22%20slang%3D%22de-DE%22%3EReferences%20to%20different%20files%20via%20formulas%20stopped%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2081857%22%20slang%3D%22de-DE%22%3E%3CP%3EHi%20Community%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20an%20excel%20file%20i%20want%20to%20refer%20to%20lots%20of%20different%20data%20from%2015%20other%20files.%20Due%20to%20lots%20of%20different%20sources%20in%20those%2015%20files%20i%20solved%20it%20with%20formulars%20like%20this%20%3DSUM(%22%5B%22%26amp%3B%22%26amp%3B%3CSTRONG%3EC49%3C%2FSTRONG%3E%26amp%3B%22%5DJahr_PV_Sp'!%24M%22%26amp%3B'%22!B8%26amp%3B%22%3A%24M%22%26amp%3B''!'C8))%2F4%3C%2FP%3E%3CP%3Ewhere%20in%20%3CSTRONG%3EC49%3C%2FSTRONG%3E%20the%20File%20Name%20is%20%3D%20Energiekonzept_Gemeinde.xlsx%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20was%20working%20until%20i%20made%20some%20file%20transfers.%20--%26gt%3B%20The%20Files%20with%20correct%20names%20are%20still%20in%20the%20same%20folder.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20some%20of%20them%20are%20still%20working%20some%20are%20not%20getting%20any%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20general%20i%20always%20had%20to%20open%20the%20files%20to%20activate%20the%20reference.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20this%20stopped%20working.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20in%20the%20screenshot%2C%20two%20of%20them%20are%20still%20working%2C%20but%20all%20the%20other%20files%20are%20not%2C%20but%20the%20forms%20are%20all%20the%20same..%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Jacob_Woeginger_0-1611227322051.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F248007i0568101D464D9AC1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Jacob_Woeginger_0-1611227322051.png%22%20alt%3D%22Jacob_Woeginger_0-1611227322051.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20you%20can%20help%20me%20on%20this%20one%20%5E%5E%20Otherwise%20im%20in%20big%20trouble....%3C%2FP%3E%3CP%3EAll%20the%20Best%3C%2FP%3E%3CP%3EJacob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2081857%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2082078%22%20slang%3D%22de-DE%22%3ESubject%3A%20References%20to%20different%20files%20via%20formulas%20stopped%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2082078%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937337%22%20target%3D%22_blank%22%3E%40Jacob_Woeginger%3C%2FA%3E%20i%20kind%20of%20found%20out%20what%20the%20problem%20could%20be%20but%20i%20have%20no%20clue%20how%20to%20solve%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDue%20to%20the%20transfer%20of%20files%2C%20in%20the%20backround%20some%20references%20of%20the%20origin%20%2Fplace%20of%20those%20files%20are%20saved%2C%20because%20i%20have%20changed%20those%20files%2C%20it%20cannot%20find%20them%20no%20more.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20whole%20problem%20would%20be%20solvable%20if%20i%20would%20create%20a%20new%20file%20with%20entering%20all%20formulas%20again%20and%20never%20changing%20the%20files%2C%20unfortunatly%20i%20cannot%20simply%20Ctrl%20a%20Ctrl%20c%20all%20of%20the%20sites%2C%20because%20excel%20says%20it%20cannot%20do%20so...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20possibility%20to%20edit%20the%20sources%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%20and%20thanks%20for%20your%20help%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJacob%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi Community 

 

In an excel file i want to refer to lots of different data from 15 other files. Due to lots of different sources in those 15 files i  solved it with formulars like this  =SUMME(INDIREKT("'["&C49&"]Jahr_PV_Sp'!$M$"&'#'!B8&":$M$"&'#'!C8))/4

where in C49 the File Name is = Energiekonzept_Gemeinde.xlsx 

 

This was working until i made some file transfers. --> The Files with correct names are still in the same folder. 

 

Whereas some of them are still working some are not getting any data. 

 

In general i always had to open the files to activate the reference. 

 

Now this stopped working. 

 

As you can see in the screenshot, two of them (Bäderzentrum and Feuerwehr) are still working, but all the other files are not, but the formulars are all the same..

Jacob_Woeginger_0-1611227322051.png

 

Hope you can help me on this one ^^ Otherwise im in big trouble....

All the Best 

Jacob

1 Reply

@Jacob_Woeginger  i kind of found out what the problem could be but i have no clue how to solve it. 

 

Due to the transfer of files, in the backround some references of the origin /place of those files are saved, because i have changed those files, it cannot find them no more. 

 

The whole problem would be solvable if i would create a new file with entering all formulas again and never changing the files, unfortunatly i cannot simply Strg a Strg c all of the sites, because excel says it cannot do so...

 

Is there any possibility to edit the sources? 

 

Best regards, and thanks for your help, 

 

Jacob