Forum Discussion
Linking Workbooks Automatically & Macro To Share With Clients
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.
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.
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.
