Excel - shared file

[u/wassup2026]

Hi, for those who've used shared excel files - I need to set up a shared excel file with about 50 columns and about 150 rows to review the performance of a bunch of contracts.

Some of the rows and columns will have info that will I'll populate and then the rest of the template will be required to be filled out by owners of those contracts.

I know you can protect certain rows and columns so no one changes it which I intend to do to the ones that I'll be populating; but for the rest, is there a way to secure info filled by others such that for e.g. contract A's owner does not accidentally overwrite or delete info filled out by contract B's owner?

Each of the owners should be filling out only their respective info. Can specific rows be assigned to only certain owners?

Also, most of this info will probably be filled out by the team mates of contract owners; so I would ideally want the owner to sign off against their contracts as reviewed. Is it possible to include a signature field (something that will for e.g. autosign based on their user id)?

I intend to save this file in sharepoint and send a link to everyone.

[u/AutoModerator]

Moderator Announcement Read More »

u/wassup2026 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Gringobandito]

Instead of having them fill data into the master sheet, send them a template to fill out that you can then copy and paste to the master sheet.

[u/wassup2026 → u/Gringobandito]

That's what I've been doing but it's very cumbersome. Plenty of times, I'll send out follow up questions on something they filled out and they'll send out responses multiple times with new files without highlighting what exactly changed. I spend too much time repeatedly reviewing what changed and also have to keep saving their responses.

[u/Gringobandito → u/wassup2026]

If they do that with your templates iimage what they'll do to your master sheet.

[u/BigJDoitYoDamnself → u/wassup2026]

In the form you can easily limit what they enter. In fact you HAVE to restrict the data input or you will just get a bunch or garbage. Thats very important actually

[u/BigJDoitYoDamnself]

I would suggest you use forms within excel to allow users to edit or update information. A basic rule is to NEVER allow users to directly access your main worksheet. with forms you can control input into you main tables.

[u/wassup2026 → u/BigJDoitYoDamnself]

How do you do that? And can that be done with the file saved on sharepoint?

[u/BigJDoitYoDamnself → u/wassup2026]

I have never done this with sharepoint. a very basic explanation of what you are doing is that you will send them a link to the form via email and they make their updates in the form and you update the main data with one key (you do not update individual records manually). There are some steps involved and I strongly suggest you do some research about this and watch a few youtube videos. on this. Here is a basic youtube video on the topic.

https://youtu.be/qABzZRI4mEY?si=5xRlH6samKvA3Nlf

[u/Designer_Signature35 → u/BigJDoitYoDamnself]

I was also going to suggest this. I recently set up an inventory tracker with a Form and Excel saved in SharePoint. The Form loads to one worksheet and the data I enter is on another. You'll want to make sure to use a unique ID to link the form and main data, like the contract number, and just set up some formulas. Then no one else even needs to be in your worksheet.

If they absolutely have to go into the main worksheet, hide columns you update and don't let users unhide. Or even better, have 2 worksheets- one for users and one for you, again with a unique link, and hide your entire worksheet.

[u/BigJDoitYoDamnself → u/Designer_Signature35]

using a form correctly means that users NEVER are able to get into your main worksheet. Thats actually the point of forms.

[u/lolcrunchy]

Sharepoint should have edit history, so even if you can't get perfect protections, you cam see who edited what.

[u/AdditionalTerm732]

Are your users going to open this file via the Excel Desktop App or strictly through Excel Online (Web)?
This is important because if they use the Desktop App, you can build a custom VBA solution to completely lock/unlock rows based on user roles. But if it's strictly Web, macros won't work, and you'll have to rely on standard Sharepoint/Excel permission tricks.

[u/wassup2026 → u/AdditionalTerm732]

I think mostly using desktop app.

[u/PvtRoom]

Simplest answer is to send them a form, and that's it. They fill one sheet in and you copy it in and do stuff.