Best Practice: Sharing a Report

Art Schneiderheinze

I have several sheets that contain course development information. Some of the information is private and not intended to be shared .

I created a report for a vendor that pulls information from one sheet (and that one sheet pulls data from 4 or 5 other sheets).

Our goal is for the vendor to update the Status column in the report for each course, so that status update appears in our main sheet. The report only has columns that are important for the vendor to see. 

However, I shared the report, and the vendor sees nothing. I am guessing this is because the vendor does not have access to the supporting sheets.

I read that I could hide the columns in the main sheet so the vendor cannot see them if she were to open the sheets. But this is not a good practice because my team whose access to all of the sheets, will be making updates as well, and will need to have columns unhidden.

Has anyone used an approach to share a report without sharing the sheets if shared preventing the person for whom the report is created from seeing private information in the supporting sheets?

Andrée Starå

Hi,

Yes!

However, I shared the report, and the vendor sees nothing. I am guessing this is because the vendor does not have access to the supporting sheets.

Has anyone used an approach to share a report without sharing the sheets if shared preventing the person for whom the report is created from seeing private information in the supporting sheets?

If you share the "Master Sheet" (the one pulling data from the other sheets), it should work. The Vendor has to be shared to the Master but not the others that the links are coming from.

Would that work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

simwra

Hi.

Yes, we have exactly the same scenario where we do not want the user to have access to the master sheet and hiding columns isn't a solution as those columns become visible again is the user downloads the sheet to excel.

Our solution is to create an intermediate sheet with linked cells or lookups to the columns/rows you want them to see.

You can then either choose to give them access to this sheet or create a report from this sheet.

I hope that helps.

Regards,

Simon

 

simwra

Hi.

Yes, we have exactly the same scenario where we do not want the user to have access to the master sheet and hiding columns isn't a solution as those columns become visible again is the user downloads the sheet to excel.

Our solution is to create an intermediate sheet with linked cells or lookups to the columns/rows you want them to see.

You can then either choose to give them access to this sheet or create a report from this sheet.

I hope that helps.

Regards,

Simon

 

Art Schneiderheinze

To clarify,I have already have a "master" sheet, in which my team manually enters some data and other data is pulled from supporting sheets.

The report I share with the vendor pulls from columns in this master. However, since the vendor does not have access to master sheet, no data appears in the report.

You are saying that

(1) I have to share viewing access to the "master" sheet from which data is pulled but NOT viewing access to the supporting sheets that help to populate the "master" sheet" 

OR

(2) (Per other's recommendation) I create a new sheet that links to just the shared information from the "master" that is used in the report, and share that new sheet with the report?

In essence, the vendor ONLY needs editing access to the report (to update the status column) and viewing access to a sheet (my "master" or a new sheet that links to data in the "master")? The vendor does NOT need access to any supporting sheets that help populate the "master"?

Thanks! The "new" sheet idea could work. This way I could keep the vendor from seeing private information in the "master" and include only those columns that ultimately are included in the report (including the "status" column).

What's the best way to link the information into this new sheet?

Andrée Starå

Ok. I've answered/commented the questions below.

To clarify,I have already have a "master" sheet, in which my team manually enters some data and other data is pulled from supporting sheets.

The report I share with the vendor pulls from columns in this master. However, since the vendor does not have access to master sheet, no data appears in the report.

You are saying that

(1) I have to share viewing access to the "master" sheet from which data is pulled but NOT viewing access to the supporting sheets that help to populate the "master" sheet" 

OR

(2) (Per other's recommendation) I create a new sheet that links to just the shared information from the "master" that is used in the report, and share that new sheet with the report?

This seems like the best option because your Master Sheet contains information that the Vendor shouldn't be able to access.

In essence, the vendor ONLY needs editing access to the report (to update the status column) and viewing access to a sheet (my "master" or a new sheet that links to data in the "master")? The vendor does NOT need access to any supporting sheets that help populate the "master"?

Yes, but I think that you need to give them editor permission or they won't be able to update the status. You can lock all columns that the Vendor shouldn't be able to edit.

Thanks! The "new" sheet idea could work. This way I could keep the vendor from seeing private information in the "master" and include only those columns that ultimately are included in the report (including the "status" column).

What's the best way to link the information into this new sheet?

It sounds like the easiest and fastest way would be thru cell linking.

Hope that helps!

Best,

Andrée

Art Schneiderheinze

I created a new sheet, and then in first cell of each column, linked to the column in the master. It carried over the data. 

How can I replicate the conditional formatting from the master in this new sheet now? I looked at just re-creating it, but all of the options were not available. (For example, in the master, for the Status column, there are five possible updates, and I set conditional formatting in the master to change the color of the cell based on the status. In the new sheet, linked to this Status column, currently :Not on Track" is the only value that appears, so when I looked at re-creating conditional formatting, I could not set it up for all possible statuses because only one appears currently.)

Then, in the report, I don't see the conditional formatting because the report links to this new sheet, not the master (which has the conditional formatting).

Art Schneiderheinze

Does your master or the new sheet use conditional formatting? I have that in my master, but when I linked columns to my new sheet, I do not have it anymore. I looked at re-creating it in the new sheet, but I cannot re-create all of the rules because currently in the new sheet those conditions do not exist yet (e.g., in master, a drop down has 5 options, so I have a rule for each option while in the new sheet, only the first option appears, since it is the current status of all projects, so I cannot re-create rules for all of the options since they aren't available yet.)

Paul Newcome

To replicate the conditional formatting you will need to enter "Test" data. Basically one row of each possibility that will allow you to set the conditional formatting but can then be deleted (the conditional formatting will remain once the test data is deleted).

Theresa Goodwin

What about making use of update requests so the vendor does not see the sheet at all but just the items you are requesting that they update?

Art Schneiderheinze

That worked! Thanks!

Paul Newcome

Happy to help! 

Andrée Starå

Great to hear you got it working!

Best,

Andrée