Multiple users (around 6) updating one master workbook. What are my options?

[CBG_Russ]

Hello and thank you in advance for taking the time to look at this problem.

I am needing to build a vehicle delivery sheet that will have around 50 columns and will contain macros. I have done this multiple times and am very happy with how to achieve this but the problem we have is that we need around 6 users to update this sheet but we don't want to be asking who is in the workbook all the time to come out so another user can update it.

I know you can share a workbook but from reading a few other forum posts it looks like that will cause some of its own issues.

My other thought and preferred method would be to use VBA and update a Master workbook that everyone updates from 6 individual workbooks that each user would use exclusively but not sure how I would deal with multiple updates.

Are there any solutions that will make this work and as seamlessly as possible to the users?

I suspect most of the individual sheets will be open most of the day.

Many thanks

Russell

 

[jkpieterse]

Co-authoring seems to be the better option. But for that to properly work you need the workbook to be in a Onedrive for business folder (or in Sharepoint) as part of an office 365 license.

 

[CBG_Russ]

Thank you for your reply. Will Macros work OK with co-authoring and what happens if a macro sorts the sheet?

We don't currently have Office 365 but may need to look into it.

 

[dmt32]

Hi,
as already suggested, co-authoring in the later versions of Excel is the way forward for requirements like yours. However, for those without this, your suggestion of a Master workbook (database) & individual workbooks writing their data to it is doable.

Have a look here: https://www.mrexcel.com/forum/excel-questions/1005007-standard-entry-another-worksheet.html

#Post 10 for an example of code I helped another with here who had similar requirement & see if it can be adapted to your specific project need.

Hope Helpful

Dave

 

[jkpieterse]

That is one of the limitations of co-authoring: If any user sorts (or filters!), all users get that same sort applied within a few seconds. I guess a two-workbook approach would be better in this case.

 

[SpillerBD]

or you go to Access to handle the user input. You could still use Excel or PowerBI/PowerPivot for any analysis.