Forum Discussion
Linking Active Workbook to Closed Workbook
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.@This is the structure. It would be a lot easier to show people. But imagine the blank workbook is an inspection report. so for each site it is going to have different clientele, different locations, different things within the body but the contact personnel should all be the same.
- The source list feeds the blank workbook in folder 1.
-Technicians then copy this blank workbook and put it into their inspection folder that they are working on and start filling it out for the relevant site that they are working on.
-They should be able to see the current employees and contact info from the client in their copied versions as they should all be linked to the source list back in folder 1 where they came from.
-They then complete the work, finish the report and upload it to our clients sharepoint site to be submitted. Hope this helps
Regards and thanks.
