Row-Level Security: Different rows for different users

Highlighted
New Contributor

Hello,
This is my first post.
I am trying to arrange a worksheet in excel for my team. I would like to use it to assign jobs (populate the left columns) and have the team members give status updates on them (in their rows on the right hand side). There are 6 of us in the team, so if I can assign a job to an individual then give only them the access to provide information on the job, that would be ideal.

This way we can have one document that all team members use to provide updates on their jobs.

This link (https://support.microsoft.com/en-gb/office/lock-or-unlock-specific-areas-of-a-protected-worksheet-75...) has helped my understand where to go, but the sheet still looks the same. If someone has an example file they could post on here that would be amazing. Also, if there is a better program for this I am open to recommendations too.

Sincerely,
PJ_Canuck

5 Replies
Highlighted

To be more specific:
Rather than manually selecting the range that a user can edit, I would like the user to be able to edit a pre-determined range if the job is assigned to them. In other words, if I have an "Engineer" column where I assign the job to a member of the team, and I put Tom in row 5 of the "Engineer" column, I would like only Tom to be able to fill in the cells to the right of the "engineer" column in row 5. Is this possible?

Highlighted

@PJ_Canuck 

I suspect you are into macro writing if you wish to invoke different protection rules depending upon the user credentials.  

 

One site you might like to look at belongs to @Craig Hatmaker .

https://sites.google.com/site/beyondexcel/project-updates/cursorcontroladd-inoffersbetterprotection

Highlighted

Hi @PJ_Canuck

 

The solution you are looking at requires you to select a range, add a password for that range, then give the password to whoever you want to edit that specific range. You can have many ranges, each with its own password. If you want to expand a range (as in adding another assignment) you must repeat the process. To me, that's a lot of work. 

An alternative without VBA programming is to give each team member their own, identically laid out workbook which they would store in their private directory so no one else has access. The workbook would include a user specific Power Query (PQ) query which would pull their assignments from your master (you must grant read-only-access to each team member) into their workbook. Each team member would then make entries into their workbook. Your master workbook would then use a PQ query to poll each team member's worksheet and consolidate data. 

Pros: 

  • No one can alter anyone else's data.
  • Everyone can be in their workbook at the same time.
  • Team members only see what pertains to them
  • Polling team member workbooks is a matter of clicking Data > Refresh All on the master
  • No VBA required
  • No passwords required

Hope that helps.

Highlighted

@Craig Hatmaker 

Hi Craig. Good to hear from you.

Is there a point at which you would introduce a relational database table to the party?

My thought is that such a strategy would enable team members to commit data when they are ready.

Highlighted

Hi @Peter Bartholomew, always good to hear from you.

 

Both methods allow team members to post whenever they are ready. But I would be looking at a database when the number of records exceed more than a couple of thousand or team members exceed a couple of dozen. 

 

BTW - Daughter Julia will probably be moving to the UK next year with her soon-to-be husband. So I'll probably have more opportunity to visit.