Segmented Sheet Permissions

Wesley De La Harpe · 09/22/20

Hello,

I have a sheet which is a cradle to grave view of the lifespan of a deal.

The problem is each of the three departments have a different permission level.

In the diagram below:

Buyers have Modify rights to Columns Pri,1,2 but cant see Columns 3,4,5,6

Operations have Modify rights to Pri,1,2,3,4 but cant see 5,6

Finance Modify to All.

There are alot more columns and groups, but this simplistic view will convey my problem.

This would be easy if smartsheet let me set permissions on a column level, but another plan will have to be made since it doesnt.

I initially planned to use a report for buyers and a separate report for operations since it allows bidirectional editing of the data and i can restrict the columns in each view - this would work perfectly for me, but the problem there is the data does not show up in the report unless those users accounts are added to the source sheet as editors which we cant do since they are not privy to that full sheet. When they log in it shows the spreadsheet in their list and they can open it.

i tried publishing the report, but the link is either open to the world or only locked down to the people "in your account" which means a buyer can use the link to view a operations report which we cant have. also it is read only if i remember correctly.

I also tried to use multiple sheets where Buyers had a sheet and their columns were inserted via linked cells into a spreadsheet for operations which included their own columns and all those cells were linked cells into the finance sheet which also included their columns. this worked fine in theory, but we lose the bidirectional editing capability since you cant edit a linked cell.

similarly tried to use vlookups between the sheets but also lose the bi directional editing.

I also tried to pull a report from these individual sheets but cant get around having duplicate data (multiple rows for one deal)

Please can someone help, I have researched as much as i can to try get round this and am stumped.🤦‍♂️

Kind regards,

Wesley.

Andrée Starå · 09/23/20

Hi @Wesley De La Harpe

A solution that would be perfect for this use-case is the premium add-on, Dynamic View.

Is that an option?

More info:

https://www.smartsheet.com/product/dynamic-view

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Wesley De La Harpe · 09/23/20

Hi Andre,

Thanks for your reply.

Unfortunately not.

The power Pivot add on which I needed when I found out we cant do pivots in smartsheet, costs so much (in south african rand currency conversion from dollar), it would be cheaper for me to employ 3 people for a year and do it manually than pay the 5000 USD per annum. just to do pivots which excel has been doing for ages.

Oh and dont forget I already have to upgrade to business plan with 3 users licensed @ 25$ each even although I only need one license - an added 50$ that i dont need, just to be able to use these "add-ons".

this isnt practical. There must be another way, please - this is crazy expensive.

Kind Regards,

Wesley.

Stefan · 09/23/20

Hi @Wesley De La Harpe and greetings to ZA from Germany!

If you can't afford Dynamic View (really the best solution for this case as Andrée said), which I completely understand, then your idea to use cell-linked sheets is the next best approach.

I once did implement a solution like that for a customer also not able to afford (well actually not willing ;-) DV. The customer had one big sheet for all and everything about proposals for new products in the pipeline. They needed feedback in the sheet from sales people in various countries and various people from different departments had to provide input for some cells.

Prerequisite was a clear separation between the user types and which columns they need to edit. The challenge was the handling of new rows, as they need to be present in every sheet and cell links don't come automatically. This challenge could be handled organisational, or with additional workflows and VLOOKUPs etc.

We ended up with kind of a column sequence along the chronology of a new product process where the responsibility for defined data input was clear and easy to assign to people and "their" columns.

Hope this helps a bit

Stefan

Wesley De La Harpe · 09/23/20

Hi Stefan,

Thank you for your time to send me a reply.

Would you be able to give a bit more detail on how I might try achieve the same objective in my example?

how did your workflow and architecture look?

Kind Regards,

Wesley

Stefan · 09/23/20

Hi @Wesley De La Harpe,

I try, though it's been quite some time ago.

First step was to get a clear image of the process. WHO needs to put WHAT kind of information WHEN into WHICH column/cell. Goal was also to get unique input columns for different user groups.

Then we modified their big sheet (a copy) to have all the columns required to take the input along the chronology of the process. On top of the sheet we built some reports and dashboards for different stakeholders. We also created some workflows (notifications about new products, changes etc.) to get rid of repetitive tasks.

When everything looked ok in the big sheet, we created copies of it for every target group. In these sheets we removed unwanted columns and had the read only columns linked to the big sheet and locked. The columns required to be editable by the group were linked to from the big sheet.

Then we had some dashboard for a target group showing a report with a tailored selection of the data from their sheet and a link to the sheet for easy access. For external people we first published their sheet (editable) but later switched to Smartsheet non licensed accounts provided by the HQ so you could better see who made a change.

Requires an internal Smartsheet champion to hold control of this setup and introduce new employees to the process and the way the sheets work. One of the duties is to add new rows to the big sheet and then link it to the other sheets. We reduced the time needed for this by using unique IDs for new rows and VLOOKUP or INDEX/MATCH in the other sheets to autopopulate cells based on the ID.

Obviously this can produce some manual work if you have frequent additions to your big sheet. At some point using DV will be a better value.

Hope this provides some inspiration for you!

Greetings

Stefan

Andrée Starå · 09/25/20

@Wesley De La Harpe

You're more than welcome!

I saw that Stefan answered already!

Let me know if I can help with anything else!

Best,

Andrée

Wesley De La Harpe · 09/25/20

Hi Andree & Stefan,

If I am understanding this correctly, I am making copies of the sheet and for those copies i remove the columns they shouldnt see - got it, not problem there. But then you advise in using linked cells to populate the info from the one sheet to the other ? the problem there is only one party can update the cell. the other party is read only.

you say "Goal was also to get unique input columns for different user groups" but if you look at my original diagram example, Buyers, Operations and Finance must all be able to edit the first 3 columns. using linked cells means only one of these groups can edit the cell and the other two groups' sheets will be read only.

or am i misunderstanding your advice? 🤦‍♂️

Kind Regards,

Wesley.

Stefan · 09/25/20

Hi @Wesley De La Harpe,

"the problem there is only one party can update the cell. the other party is read only."

yes, that's right.

In my experience, one responsible person or party starts the process and others are invited to provide input/feedback. The input can be different viewpoints about the same topic and the responsible person needs the input for the final let's say order numbers or something else.

In the case I talked about, the customer had a big sheet with 100+ columns and some columns asking for the same type of input (let's say price proposal) were duplicated for every additional party to provide feedback. The manager and owner of the big sheet wanted to see all different feedback and then consolidate the data into one final figure.

So yes, every party/person in the end had the same view about basic sheet data from the big sheet. But for the other involved parties, there were clear responsibilities about who had to provide which input.

All theoretical and my not suit your use case. If you don't mind to describe the process, we could better understand and maybe provide other ideas.

Hope this helps

Stefan

Wesley De La Harpe · 09/25/20

Hi Stefan,

Sure, here is more info on the process.

Buyers obtain the deal and insert pertinent info like delivery dates, quoted price, delivery address etc

Operations insert their own columns data, but must be able to modify original buyers columns as well like they might change delivery address etc.

Finance insert their own columns data, but must be able to modify original buyers and operations columns as well like the agreed price.

Buyers and operations are not privy to the deal's financial to see net profit etc. so they are not allowed to see the Finance columns.

CEO is very hands on and flat management structure, so he deals with all departments personally as well as clients. he wants one spreadsheet showing this deal info from cradle (deal inception and quote price), to grave (deal closure info, net profit etc).

point is, all groups must be able to edit the columns they are privy to, but not have access to the columns they are not privy to. hope this helps.

like i said, the perfect solution is where i create separate Smartsheet reports on original spreadsheet - works like a bomb! if there is some way to get the data to display in the report without them needing to be added as editors on the original sheet then i would be sorted 😢.

Kind Regards,

Wesley.

Wesley De La Harpe · 09/25/20

The other option Is if i keep 3 separate sheets, and i create a report linking to the 3 source sheets - i might be able to find a way if i can get it to stop showing the duplicates like below.

the primary column can be set to not be updated which means i can set it as linked cell for the other 2 sheets, but then i need bi-directional editing on the other columns. think thats possible?

Stefan · 09/28/20

https://community.smartsheet.com/discussion/comment/263801#Comment_263801

Hi @Wesley De La Harpe,

nice idea, but will not remove the need to share everyone to all the sheets.

3 sheets seen from another angle might do the trick in combination with reports. Think about cascading sheets built on top of each other with reports for editing data linked from other sheets.

FINANCE-SHEET: Col1, Col2, Col3, Col4, Col5. No one has access to this sheet but CEO and Finance. Col 1,2, 3 are cell linked from ...

OPS-SHEET: has all columns needed and some cells are linked to the...

BUY-SHEET: has all the columns needed

FI has is shared to all 3 sheets, Ops only to their sheet and the Buyers sheet. Buyers can only see buyers.

If FI or OPS want to edit something in the Buyers sheet they can use a report, same for OPS.

Use a little color to make visible which columns can be edited right away and which you need a report for. Maybe put the sheets and reports on a dashboard every group.

Would that work for you?

Greetings

Stefan

Andrée Starå · 10/07/20

@Wesley De La Harpe

To add.

You could also use cross-sheet formulas (VLOOKUP or INDEX/MATCH) if new rows were added to the individual sheets because the cell-links won't update.

Make sense?

Wesley De La Harpe · 10/20/20

Hi Stefan,

I have tried your idea of "cascading sheets built on top of each other with reports for editing data linked from other sheets". The problem i find is SS doesnt combine the correct rows from each sheet, resulting in 3 rows of the same data one from each sheet..

I think the index match is also above me, all the coffee in the world didnt get my head wrapped around all that. 🤦‍♂️,, best i could do was populate all 500 rows with dummy data, perform cell link on those cells and delete the dummy data. the cell links remain while cells are blank so i will have to address this again when the 500 rows are full but thats going to have to be the solution for now.

I have demoed the option where one source sheet does cell links into the other ones. and only the relevant parties can update their own cells. they will just have to accept this since we cant afford the DV option and I cant spend much more time on this.

Thanks for your assistance, but im going to have to put this puzzle away unfinished 🤷‍♂️

Kind Regards,

Wesley.

Segmented Sheet Permissions

Answers

Categories