Sharing an Excel Spreadsheet on Sharepoint

Question

We are attempting to share an excel file among multiple users from a SharePoint site. I want to know, is it possible to set a filtered view per user?

The long version:

We're in the Community Mental Health field, and various employees need to keep up on certain training programs, at state mandated intervals. I've set up an Excel spreadsheet where column A is employee's name, column C is the Department they work for. Columns D through whatever contain dates that trainings were updated by that employee, and contain conditional formatting logic such that the background color changes from green if item is up-to-date; to yellow, if it will expire/must be renewed in <30 days; to red, if it's past due. Everything is working great there.

I've given myself, (as IT coordinator) my Supervisor, (As head of IT) and the three HR employees access to the SharePoint site and they can access the sheet and make updates.

I know I can give additional employees, (Department heads) read-only access to the whole spreadsheet, I would like to know if it's possible to restrict them to a filtered view that only shows rows where C=$SpecificDepartment.

Questions:

1. Can users be granted read-only access to the file, such that the file will be filtered as set forth above? this would allow them to access the spreadsheet in real-time
2. Can a CronJob/Windows Task scheduler command be set to create a spreadsheet with only the relevant rows and save that as a new spreadsheet that the department head can gain access to, either through SharePoint site, email attachment, or direct upload and overwrite of old file on their OneDrive storage?
3. is there a better/more efficient/more automated way about going about this access for the department heads?

Answer 1

Hi Timonthy,

I’m glad to be here and share this suggestion with you.

First, please allow me to do a summary about your requirement. Per your description, you want to let 3 HR to access this worksheet but can’t edit, meantime they need to see filter view after they open the worksheet in SharePoint site. You can your head of IT can edit this file.

Second, I will provide some suggestion about your question. 

1. Open Excel in SharePoint Online will open that file in Excel Online. There is not auto-filter feature which is based on SharePoint site permission. Because the SharePoint permission only can control can you access this file, edit or read. Excel Online can't auto-filter based on that.
2. As your requirement, you want to let HR can only see view of specific departments, you can filter column C (Department) and create different sheets in one document. To keep this filtered view. Upload this worksheet to SharePoint library and grant unique permission to this worksheet. Based on your requirement to share with different people.(Give HR Read-only permission, you and head of IT have Edit permission.)

3. If you don’t want to HR can see the data of other department, you can give the password to other department sheet, so that HR can’t access the protected sheets. They can only access the specific department filter view. 
4. When you save the file into SharePoint Online and edit it in Excel Online or Excel client, it will update automatically. You can save this worksheet from Excel client to SharePoint site-> Library directly.  
5. In my opinion, the better way to restrict who can access different filter view in SharePoint Online is to separate the filtered results and give different permission. 

For more information, you can refer to these articles:

Customize SharePoint site permissions

Understanding permission levels in SharePoint

Protection and security in Excel

If there is any misunderstanding, please help me to point out and we will investigate further to give a better solution for you. 

Regards,

Ivy