Linking Workbooks Automatically & Macro To Share With Clients

[tzaharia]

I am hoping for some super Excel Jedi to help me out. What I am doing is beyond my capabilities, especially being that I am a beginner in excel and barely understand scripting.

My company has many clients. We want to send out a weekly update through some excel template. The idea was that in one workbook we have many tabs each dedicated to a client. We wanted to be able to share that client tab with that client without giving them access to the entire workbook. Essentially we wanted to share their one tab, and nothing else.

This is what I've come up with, but need assistance in getting it going.

I've figured that I can create a client workbook for every client and put it in their respective folder. This client workbook will be linked to a master project workbook that we update. The master project workbook is in a main folder where it is more accessible. When the client workbook has been updated through its source material, we share it with that specific client.

Example: I open master workbook, go to Client A tab, change the data in the cells and that information automatically updates on their respective client workbook that is in their own folder. Then I share client A's workbook with the client. 
Client A then receives an email with a share link to their excel spreadsheet that is read only.

That is the main goal.

The issues I am having are:

1) I need to figure out a functional formula that links the data from the Master project workbook to the clients. I've used the "=" function in the clients cells and then selected the Master cells and hit enter so that it links it, but this seems temporary as I opened the files today and they no long have that function so they aren't updating linked data from the Master workbook.

2) This also requires a macro connected to a command button that allows me to share that workbook with the client. I've created a personal macro workbook to save all my macros, but I need the command button to be on the Master workbook within each client tab dedicated to their respective information that upon click will share the Client workbook that tab is connected to.
 

I should also mention that the client workbooks would be in their respective folders NOT OneDrive which becomes an issue apparently when you've turned Autosave off and can no longer "Share" the file.

3) Need to figure out a solution to sharing without requiring OneDrive

[#2]

@tzaharia 

I may not be understanding your full need here, but there 's one crucial line in your description that may point to a resolution.

You write "Client A then receives an email with a share link to their excel spreadsheet that is read only."

If it's read only, I find myself wondering why they need to see it in Excel. Could you not just print to PDF and send them the PDF version. All nicely laid out, and pretty much by definition "read only."  (Granted, there are PDF editors out there, but that's another story.)

By the way, without knowing what all is in your master workbook, and given that your self-description is that of a "beginner in Excel"--it might well be the case that you could have a master workbook that consists of the master database--containing all records of all clients--and a single output format, to be populated one at a time with any individual client's data. There are various functions that could be used to accomplish that. If you upload a sample of your master data and a client update sheet, some of us could demonstrate for you how VLOOKUP, or INDEX and MATCH, or some similar functions--perhaps Pivot Table or Power Query--could serve to meet your need. If you have hundreds of client updates to produce each week, it would be possible to write a macro to automate that....but let's cross that bridge later.

[#3]

@mathetes Thank you for getting back to me. Before I get into your response I'll say this.

I have realized that I cannot use the workbook share solution because I need to have the workbooks open at the same time while the information is being modified in order to see those changes.

My hope was that I didn't need to open the Client Workbook and that I would only need to handle data through the Master Workbook. Needing them both open negates the purpose. 

To hit on your reply;

the goal was based around my bosses needs and preferences. They did not want a PDF image being sent out. They wanted a single share option where the client would receive the share link, then they could always access their information and we could just send reminders to check the summary as it is updated weekly. Basically they want as simple an update process as possible. 

My Master Workbook has nothing in it as we were looking for solutions prior to filling any information out. The most I have is a table template that we can fill in per client per tab.

At this point because my bosses aren't keen on my workarounds or other solutions, I'm essentially back at square one. 

I do appreciate the help though.

[#4]

@tzaharia 

FWIW, having worked for bosses, and having been one too, I think it's often appropriate to respectfully challenge their preconceived notions, especially if (instead of calling it a "workaround") you speak of, say, distributing a PDF as an alternative and actually easier way to send out those weekly updates. Point out you'd be meeting the goal of communicating with, and updating, the client. That is, this IS the simple update process.

FWIW, at one point in my career (I retired a long time ago), I was in charge of the HR and Payroll database of a major corporation. There were occasions when I still had to challenge higher level execs who had preconceived notions of reports they "needed"--my goal (when those occasions arose) was always to show that there was a better way to accomplish the very thing they wanted to do. Not everybody likes to be questioned; I certainly realize that, so you do have to pick the right time, but make it a matter of saying yes to the goal, not just "No" to their way.