Apr 14 2020 11:38 AM
Apr 14 2020 11:38 AM
I currently have an excel document where every account team within our organization enters their forecast and actuals. I am having issues with people overwriting each others information in the tables, as well as the file someone giving weird ready only messages and not allowing users to save.
Is there an easy way to somehow segment each account/user to their own version so they may not overwrite others and to avoid the issue where the file isn't allowed to be altered du
Apr 14 2020 11:51 AMSolution
Hi @ukfan9 ,
This is a common issue. Complicated spreadsheets or just ones with many authors are not suited to co-authoring in my opinion.
I have had the same issues that you mention. 2 ways around this:-
1. Enforce Checkin/Checkout on the file, one person editing at any one time.
2. Convert the data entry into a SharePoint list and export the data to excel or PowerBi to do the reporting.
Hope that helps.
Apr 14 2020 02:06 PM
@Andrew Hodges Thanks for the quick response! I already have the info feeding into PowerBI for visualization but the input is what I am struggling with.
Can you please elaborate more on the list option? Unfortunately using the checkout issue there are always people that linger or forget to sign out. Everyone waits until the last day to update so that would be an issue.
Apr 14 2020 02:39 PM
The list option is to create a custom list in SharePoint(Add an App), create the same columns as you have in the excel workbook and use the custom list as lite database. As only one row is locked you can have a lot of users editing data at once before you would come across an issue.
PowerBi can consume a SharePoint list so that should be fairly straight forward for you.
Apr 14 2020 02:47 PM
Yes, but not recommended, the way you would normally do that if you had to would be to write a workflow on creation of each row(item) and permission the row based on who entered the item or some value they entered. It gets a bit complicated so if you can stay away from that.