Link Excel files for automatic synchronization
pete
-
jeanot017 Posted messages 96 Status Membre -
jeanot017 Posted messages 96 Status Membre -
Hello,
I have about ten linked Excel files, meaning the data from file A is used to build file B. The data from C is useful for D. D and B together create E; in short, I think you get the idea!
Today, to move from one file to another, I have to use copy/paste, and it's eating up a lot of my time.
What I would like to do is create links between all these files so that they synchronize automatically when I modify one of the source files.
Please note that these are monthly data and I need to create a new one every month!
Do you think this is possible in Excel?
Thank you in advance for your help!!
Pete
I have about ten linked Excel files, meaning the data from file A is used to build file B. The data from C is useful for D. D and B together create E; in short, I think you get the idea!
Today, to move from one file to another, I have to use copy/paste, and it's eating up a lot of my time.
What I would like to do is create links between all these files so that they synchronize automatically when I modify one of the source files.
Please note that these are monthly data and I need to create a new one every month!
Do you think this is possible in Excel?
Thank you in advance for your help!!
Pete
2 réponses
Pete, good evening,
of course it's possible, the basis of all this is the wonderful PASTE SPECIAL function, with plenty of additional functions in the popup menu that opens, but especially the "PASTE LINK" button, and you're all set.
In the project you're presenting to us, which seems to be monthly management sheets, a piece of advice: create a monthly sheet matrix with all calculations and functions inside before the beginning of the year, and duplicate it 12 times, then create an annual summary sheet to which you will direct all the links. (The fan structure is more flexible and safer than the cascading structure; if you break a link, it doesn't undermine the entire hierarchy.)
In the annual sheet, you will be able to do all the final calculations using the target cells, which poses no problem
And we can even repair, modify the source, delete the links in the Edit-->Links menu of the sheet to which the links point
And at a higher level, we can automate all this, add buttons, create triggers, dialog areas, etc., in Visual Basic, but that's another story....
I clarify to be clearer: copy a cell from the first sheet, and go through the Edit-->Paste Special menu to the second sheet
The link is only one way from the source cell to the target cell, if we write in the latter, the link is broken
But there can be links in both directions between the 2 sheets as long as the concerned cells are not the same;
Final clarification: these links are not HTML links that can also be pasted into Excel sheets, they are proprietary links of the Microsoft application
Best regards,
Jean
of course it's possible, the basis of all this is the wonderful PASTE SPECIAL function, with plenty of additional functions in the popup menu that opens, but especially the "PASTE LINK" button, and you're all set.
In the project you're presenting to us, which seems to be monthly management sheets, a piece of advice: create a monthly sheet matrix with all calculations and functions inside before the beginning of the year, and duplicate it 12 times, then create an annual summary sheet to which you will direct all the links. (The fan structure is more flexible and safer than the cascading structure; if you break a link, it doesn't undermine the entire hierarchy.)
In the annual sheet, you will be able to do all the final calculations using the target cells, which poses no problem
And we can even repair, modify the source, delete the links in the Edit-->Links menu of the sheet to which the links point
And at a higher level, we can automate all this, add buttons, create triggers, dialog areas, etc., in Visual Basic, but that's another story....
I clarify to be clearer: copy a cell from the first sheet, and go through the Edit-->Paste Special menu to the second sheet
The link is only one way from the source cell to the target cell, if we write in the latter, the link is broken
But there can be links in both directions between the 2 sheets as long as the concerned cells are not the same;
Final clarification: these links are not HTML links that can also be pasted into Excel sheets, they are proprietary links of the Microsoft application
Best regards,
Jean
My problem is that when I duplicate the files, the source remains the original file.
For example, I do a copy/paste with a link (from A to B), it works; if I modify A, B is modified. I put these two files in folder C. I duplicate the folder into C'.
Then, if I modify A', B' is not modified because its source remains A, right? How can I resolve this issue?
Best regards,
Pete.