Jump from SP list to another SP list

Question

I have an organization with 10 different departments. Each department has their own list where they add reports.

The CEO wants to view all reports from each department in one Main list and be able to go to department lists from the Main list instead of having one list that each dept has access to and uses filters.

Different departments can upload different documents and data for the same Report #.

Each list has these columns: Report #, Version, Notes, Attachment/Document, Modified Date, Department Name (10 choices), and Week (week one or week two choices).

In the Main list the CEO can click on the Department Name value and it will take him to that Department's list and show all reports for that dept.

In the Main list the CEO can click on the Report # value and it will take him to a list that shows data (Department Name, Notes, Attachment/Document, Date Modified) for that report # from all departments.

Is this possible and should I create document sets instead of lists? If I create document sets how do I link them to one and have him be able to jump around through simple clicks?

Appreciate any help or direction, thanks!

Answer 1

Documents are best managed in a library, not as attachments in a list.

Use one SharePoint document library. Add folders for each department and metadata columns for Report Number and the other metadata you listed above.

Create a regular view with folders to show the departments. Create another view that ignores folders and group it by Report Number.

Teach you CEO how to use these views.

You can assign permissions to the department folders so departments cannot edit each others' files.