Results 1 to 3 of 3

Thread: Tabs To Share or Data Reference Update

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Seeker tzaharia's Avatar
    Join Date
    Jan 2020
    Posts
    6
    Articles
    0
    Excel Version
    365 Office

    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.

  2. #2
    Acolyte bobjglover@gmail.com's Avatar
    Join Date
    Aug 2015
    Posts
    57
    Articles
    0
    Excel Version
    1901
    Quote Originally Posted by tzaharia View Post
    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.
    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.

  3. #3
    Seeker tzaharia's Avatar
    Join Date
    Jan 2020
    Posts
    6
    Articles
    0
    Excel Version
    365 Office
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •