Tabs To Share or Data Reference Update

I'm by no means an excel guru. Furthest from. I am comfortable enough in it for basic functionality. I do however need help with a goal that is a little out of my realm.

My company wants to create a weekly excel template that can be used to send updates to our clients.
The intention ideally would be that on one worksheet we have multiple tabs, each associated with a client. We fill in the data for that clients sheet and then send a link to that excel tab that the clients can look at. We don't want them to see the other tabs because they should not be privy to other clients information.

I don't know if there are any feature updates that would provide a solution for this, but my other idea would be to create two worksheets. One with the client tabs (Parent Worksheet), the other that is referenced by that client worksheet (Child Worksheet).
My intent would be that I fill out a clients tab on the Parent Worksheet and it automatically fills in the same formatted cells on the Child Worksheet. Then we send a link to that specific client who can open their information on the Child Worksheet. Of course a setback in this case is that I would need that information to clear out automatically as well after a given period of time or by using a formula that clears it upon use so that a week later those same clients don't click on the link and go into a different clients data.

I hope this makes sense, I've been searching for solutions for a few months.

The preference of course here is that we just figure out a way to send a clients tab link without giving them access to the other tabs.

tzaharia said:

I'm by no means an excel guru. Furthest from. I am comfortable enough in it for basic functionality. I do however need help with a goal that is a little out of my realm.

My company wants to create a weekly excel template that can be used to send updates to our clients.
The intention ideally would be that on one worksheet we have multiple tabs, each associated with a client. We fill in the data for that clients sheet and then send a link to that excel tab that the clients can look at. We don't want them to see the other tabs because they should not be privy to other clients information.

I don't know if there are any feature updates that would provide a solution for this, but my other idea would be to create two worksheets. One with the client tabs (Parent Worksheet), the other that is referenced by that client worksheet (Child Worksheet).
My intent would be that I fill out a clients tab on the Parent Worksheet and it automatically fills in the same formatted cells on the Child Worksheet. Then we send a link to that specific client who can open their information on the Child Worksheet. Of course a setback in this case is that I would need that information to clear out automatically as well after a given period of time or by using a formula that clears it upon use so that a week later those same clients don't click on the link and go into a different clients data.

I hope this makes sense, I've been searching for solutions for a few months.

The preference of course here is that we just figure out a way to send a clients tab link without giving them access to the other tabs.

Click to expand...

How about 1 workbook using Hide and Protect on the sheets? For your "clear out" piece you could use a simple macro. Just record the steps of how you want it cleared each month and save it to that workbook or a macro workbook that you already have.

The Hide and Protect tabs works to a point. The problem being if they create a new worksheet, they are able to unprotect and unhide the other sheets. Not sure why this loophole exists but if I change permissions to read only I'm wondering if that would still be an issue.

I did find an alternative solution though.

I can create a Destination workbook for each client and put it in their respective folders. Then I can build a Project Source workbook that has each clients tabs associated with their specific workbook. I can reference it back then so that I only need to update the Source workbook. Then I need a macro built for each client as well so that after I have update data for the client in their tab, I just need to select a command button with the VBA script and it will automatically share the clients workbook that is in their folder.

Thank you for the help though.