Linking Active Workbook to Closed Workbook

[Jn12345]

This seems to be a topic that no one has a good answer for unless you're the only person working on the file from one device which in 2024 is unlikely. I have seen loads of other posts bringing this up so i sort of doubt that there is an answer out there but here it goes...

I have a reporting template that is used for each different job to be completed and for now is only used by two maybe three people. There is a hidden page in this reporting template with a list of contacts and there typical contact info and roles etc... For now it isn't such a big issue, but when i get to the point where there are 5-10 people working on 5-10 of these reports it will be impossible for me to keep these tables updated so that they have all of the proper contact info hidden within them so i need to find a way to have one master list that I can update which then updates the other workbooks.

The structure:

It is on dropbox. I have a folder that all of my people can access with the blank report template. they then copy and paste it into their ongoing project and then start using it. Ideally I can have a closed file in another folder on dropbox that contains the single list and then the other copied versions can follow that list. 

I have been through the methods where workbooks need to be open which isnt a great solution, as well as the INDEX() method which was pretty good but unfortunately was only able to be used from my computer.

At this point I have no idea where to start. some people say to make a list on sharepoint, others say power query, others say VBA. i really have no idea what the best method would be. 

Thank you for your help, as always,

Jeremy

[#2]

Have you tried placing the linked file in the same folder? When you are using formula links, the easiest way to maximize the chance the link works is by ensuring both files are in the same folder.

[#3]

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.

[#4]

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.

[#5]

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.

[#6]

@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.

[#7]

If you place your list of contacts in a table and then create a range name that points to all cells in the table, you can use a direct reference to the range name (easiest while both files are open) in the hidden worksheet, similar to this:

=[file with contacts.xlsx]!YourNewRangeName

In my experience, as soon as the workbook containing this formula is saved outside of the folder structure, the link becomes absolute and should keep working, provided the user has read-rights to that location (and a working connection). Now all it should take is editing the file with contacts.
Of course the user must update links when using the file.

[#8]

@JKPieterse I agree on the range and had indeed understood that that is how he works. But I think you find that you are wrong. Do not open the foreign Workbook with that range and the referencing Workbook sees #REF!. Which is why I open all linked Workbooks from Auto_Open.

@Jn12345 You seem to be struggling with succession handling which is never easy. Also a big minefield in TEAMS.  A natural Person must be assigned a Role. That Role can then be assigned to Tasks. That way, when the Person leaves the company, a new Person can be assigned to the Role and all Tasks should automatically then show the new Person.