Share Info between multiple Excel documents

[Cookiecrumbles222]

Hey guys I know you can share info between multiple Excel sheets with 3D Formulas, Is there a way to do this through multiple excel documents For example what i want to do is say make a profit sheet for each month and have the total profit on cell say E15 and have all these docs stored in month folders then have one document pull all the data from them into one to get yearly profit. Example bleow is this possible?

 

https://gyazo.com/e263e9e0c8fea6460acdd8418e3dfd2a

[Avocado Diaboli]

Sure, you can just have two files open, type an "=" into a cell in one file and select a cell from another document. That way it creates a link between those two documents that updates every time you open the target document.

If the source document is closed and the target document is opened, it'll show you a message asking you if you want to update the linked values.

 

Alternatively, if you don't want to go into every source file and manually set the links, you can write a VBA macro to loop through all the files, copy the required data and paste it into the totals file. That'd mean they aren't directly linked and you don't get those messages you always have to confirm when opening the totals file. You can set the macro to run whenever you open the totals file automatically.

 

As an addendum though, I want to suggest not keeping individual files for monthly reports and instead collect all the data in a single file and break that out into individual sheets within the same file per month or better yet simply create a single monthly report in the same file where you collect all the data that automatically updates based on a filter. That way, you already have all the data in one place if you ever want to run a statistical analysis over it in the future without having to pull the data out of a dozen files per year. 

[Cookiecrumbles222]

9 minutes ago, Avocado Diaboli said:

Sure, you can just have two files open, type an "=" into a cell in one file and select a cell from another document. That way it creates a link between those two documents that updates every time you open the target document.

If the source document is closed and the target document is opened, it'll show you a message asking you if you want to update the linked values.

 

Alternatively, if you don't want to go into every source file and manually set the links, you can write a VBA macro to loop through all the files, copy the required data and paste it into the totals file. That'd mean they aren't directly linked and you don't get those messages you always have to confirm when opening the totals file. You can set the macro to run whenever you open the totals file automatically.

 

As an addendum though, I want to suggest not keeping individual files for monthly reports and instead collect all the data in a single file and break that out into individual sheets within the same file per month or better yet simply create a single monthly report in the same file where you collect all the data that automatically updates based on a filter. That way, you already have all the data in one place if you ever want to run a statistical analysis over it in the future without having to pull the data out of a dozen files per year. 

Bro thank you so much been trying to find an answer all day and thats exactly what i needed THANK YOU!