Lock a Sheet

Scott Medd · 01/12/17

Hi,

Is there any functionality to lock a specific sheet. I dont think there is but this would really help for the situations explained below. There are two scenarios:

Scenario 1

We have a master sheet, where all product information is pooled. there are 100 different columns and hundreds of rows. One team is responsible for inputting the first 5 columns of data and populating all the rows for that data.

Team 2 are responsible for filling in the information for the next 10 columns on all rows

Team 3 are responsible for the next 15 columns

Team 4 are responsible for the next 5 columns

etc

Now we want to make sure that only each specific team can update their columns so that the other teams do not edit their data by mistake. Therefore we use reports that give each team those base 5 columns and then the columns that relate to them.

However there is no way to stop them going into the main sheet and editing all the data. One could suggest locking cells but this doesnt work in the fact that you have multiple teams editing different data. Also you have to give them access to the sheet for them to be able to view the report.

Scenario 2

HR have a very sensitive document for bonus review. It lists all pay for all employees that is split out by managers. Each manager has to fill in their suggested bonus for each member of their team. The ideal would be to have a master sheet that could be locked and then run reports for each manager that listed their team members as then this would in turn update the master. However because you cant restrict the master what you have to do is have different sheets for each manager and then pool these togetehr in 1 report to have the full picture. This is clunky and has issues when you want to update the format of the sheets.

Sorry for the long post but its an issue that has been bugging me.

Any help would be amazing.

Thanks

Scott

Richard Rymill SBP · 01/12/17

Scott

these two scenarios are well described by you and ones that many Smartsheet users face, so very good questions to ask. My colleagues and I will digest what you have asked and while there isnt a simple answer, I hope we can provide some useful suggestions to improve these workflows. Bear with us.

RichardR

J. Craig Williams · 01/12/17

Scott,

First, some questions:

Scenario 1:

1. How and when are rows added to the sheet?

2. How many teams total do you have?

- I assume a maximum of 20 at 5 columns per team.

3. Is Team A responsible for the 5 base columns and all other teams 'consume' the 5 base columns plus have some columns (which varies by team)

4. No other team modifies data in the base columns?

5. Is there any other overlapping cells?

Depending on the answers, this might work:

1. Master sheet contains 5 base columns and only 5 base columns.

-if the base 5 columns do not contain a unique identifier, this is added as a new column.

2. Each teams sheet contains base columns plus their own columns.

3. Team A links the base columns to each team's sheet when new rows are produced.

This isn't as painful as it sounds (but is painful).

Each sheet is limited to 5000 incoming links. With "hundreds of rows", your limit for 6 base columns is 833 rows.

4. Reports pull from team sheet and base sheet based on unique identifier.

Primary drawback is 2 rows per record, which may be manageable. If the answer to (5) is yes, then that number goes up and (imo) this method has markedly less appeal.

If the primary concern is keeping people out of the sheet, then, while not preventative (which would be better), here's something:

1. Policy: No one enters the sheet directly without permission from X. No one.

2. Create a column that has TODAY() in it.

3. Create an Notification to X when the column changes.

- the column should only update when it is opened and saved. (In theory, I haven't tested this recently)

This would not prevent someone from opening and modifying, nor would it prevent them from open and looking and closing without saving.

But if they did the first, X would get an immediate notification and action could be taken because of violation to (1).

A single cell would be better, but we can't do an Alert on a cell.

For Scenario 2, I'll need to give it more thought.

Craig

Richard Rymill SBP · 01/13/17

Well said Craig, which acknowledges that there is no easy way to do what Scott wants to do, which is preserve the confidentiality of information between teams without spoiling the ease of access that Smartsheet does so well.

Scott, you and I have talked about adding Appsheet functionality to Smartsheet and if that is a route you wish to explore, then, we can meet you confidentiality requirements by providing access to Smartsheet held data, via Appsheet which is able to... only show the viewer what the Admin wants them to see, while also adding some lovely extra features like offline access, Geo located signatures etc etc. We love using these two tools together since Smartsheet introduced us to Appsheet just over a year ago. eg great for Timesheets that clock time against projects from a mobile device.

Regards

RichardR

Opeka · 02/21/18

Any update on this? I need to be able to limit who can see specific data in a sheet. I have a master sheet that is shared to both the client and my team. I then "hid" all the columns containing financial information. I then built reports for each member, showing only the columns that I wish them to see. I spent a considerable amount of time reshaping and moving data into this new format. I wanted to double check a few things, so I shared one of the sheets to my personal email address. I am frustrated to learn that when I select the “unhide all columns” button, I am prompted to upgrade and it appears as though if I upgrade I will be allowed to open up those columns in the master sheet. I will try and make sure it is clear what I am trying to do. All of the columns currently visible in the master sheet are included in each report, along with the addition of the financial columns of the specific person for whom the report is created and shared.

Welcome to the Smartsheet Forum Archives

Lock a Sheet

Comments

Categories