I have an excel database that we use to store product information. The admin of the sheet is the only person who should be able to edit the information on the sheet, which is easy to do through protecting the workbook/sheet.
There are macros in the sheet created to help streamline processes for the end users such as exporting a list of parts they need. So, while the workbook is open, the user needs to be able to make "edits" but these changes should not be saved (currently have a macro at workbook open that would reset any of these changes).
Because of the macros, this sheet is used through our OneDrive client but we are running into the issue of the duplicate files with the comp name.
How can I make it so only certain users' (the admins) changes are saved and everyone else's are discarded?
I'd like to prevent users from having to save a new copy each time they want to access the workbook and I'd prefer to not have to go to the document library and delete any duplicates every day. The perfect workflow for end users is to open the workbook from their OneDrive, get the information they need, and close it without interfering with anything an admin does.
Options I've considered but not sure if they would accomplish what I fully want:
- Have the admin check out the workbook when it's being updated (admin will sometimes have the workbook open all day so can users still access the last synced version with it checked out?)
- Set everyone's (except admins) permissions to view only in SharePoint (can the users still manipulate the sheet to get the full functionality and then it will not let it save those changes?)