Forum Discussion
Linking Active Workbook to Closed Workbook
- The only issue is that even if the file is in the same linked file to start, it will be copied from that file and pasted in a different project folder based on whatever ongoing projects we have on the go. so the source code file could always stay in the one file location along with the blank reporting template that draws from it, however, the other members of my team will be copying the blank report template and pasting it into their individual project(s) (still on drop box but just in a different location. It seems to work that when they open their sheet as long as the source file is open also, the links will work, but unfortunately that isnt really doable.
This sounds vaguely familiar. I think that the Worksheet/Workbook concept of Excel is a design accident going back 40+ years. Fundamentally, a Worksheet isn't really different from a Workbook, ie you should not have any hang-ups about disaggregating your Workbook of 8 Worksheets into 8 linked Workbooks (or any combination in between). In practice, Microsoft makes your life hell when you go down that route. One aspect of that hell is that you are fretting about a Workbook - containing your master list - being open. And you fret about this because, out of the box, Excel does not open linked Workbooks.
If you want to get to a place where 1x Workbook / 8x Worksheets becomes functionally equivalent to 8x Workbook / 1x Worksheet, you want to create yourself an Auto_Open macro that runs thru all linked Workbooks and opens them. To that end, your template Workbook must be an XLSM. When you get there - ChatGBT will be happy to help - neither you nor your users will care any longer that their Excel project consists of umpteen Workbooks. All dependencies will just pop up. (I must confess that I do not know what "pops up" in the case of a Workbook containing only one hidden Worksheet. Conceivably nothing.)
At that point, you will run up against a different problem. Unless you took the greatest care, each user's Dropbox will be in a different Windows file path from every other user. Excel links unfortunately do not recognize this concept at all. You may find - like I did - that you have to patch up file paths as you implement the exercise above.
- Just to clarify, There is one master list with a bunch of client contact info etc and then one workbook that contains approximately 13 sheets within it and a hidden page that at this point has the client info all on a hidden sheet. When a new project is started, individual people will copy and paste the blank workbook into their own folder (1 per inspection) and begin filling it out. They will select the appropriate contacts once in the workbook that correspond to the clientele who are also on the job.
Now, if the client contact changes or a new person is hired, I have to go in to each and everyones workbooks that they have (which is sometimes 20+ work books) and update the contact info in order to be relevant.. The addition of new members or people quitting can happen multiple times per week which at this point turns this into a very time consuming process.
If it is not possible to link all of these workbooks (that are essentially copies from the main workbook that is in the same folder as the source list for them) without running into issues. Maybe its best to make a CSV upload where the technicians can upload the updated list into their own copies at the start of every job. Seems like more of a work around than a fix, however, I cannot find a good fix anywhere.
