Single Excel Workbook with Multiple Users to access simultaneously

[Gajendran Yadhav]

Dear Members,
I'm working on Excel workbook to collect and maintain a set of data based on the occurrences of certain events. For instance: Hearings of cases by Court of Law.
I've users working remotely in individual excel files. They send me their file every month. I consolidate into single workbook.

Is there any possibility that I can use a single workbook for all the users simultaneously so that final consolidation work is minimized / eliminated ?

I've 49 sheets all together in the workbook out of which 1 sheet contains some basic data for List functions, other data etc. and it's accessed by only myself.

Remaining 48 sheets are divided for 24 users, each Password protected.

I can share it over Microsoft One Drive and work simultaneously. Is there any means to hide the sheets from other users.

i.e., User A should view only Sheet 1 & Sheet 2;
User B should view only Sheet 3 & Sheet 4
User C should view only Sheet 5 & Sheet 6
and so on...

How to make this possible..??

 

[AFPathfinder]

VBA/Macros would be my goto, but there are limitations based on what your delivery options are. My initial thought would be to create an import macro and an export macro. The import macro would update your main file with the updates from 24 other users. The export macro would create a separate, temporary excel file for each user, create the email, attach the file, send it off, then delete the temp excel files.

You could keep the main file on One Drive for all to users and manage passwords on all the sheets to keep the users restricted, but that could be a lot of work itself and it's not necessary a great option if data protection is important. If you tried to show/hide sheets based on the username of the user through VBA, the user could just open the workbook without enabling macros to bypass. It's also possible to remove passwords set on workbooks and worksheets.