locked
Updating Excel Spreadsheet Simultaneously RRS feed

  • Question

  • Hi SharePoint Guru's!  I've got a question I can't really seem to find documentation to address.

    Essentially, the business problem is this, an exec sends a spreadsheet to his directs, who sends it to their directs, etc. until it reaches the people who need to perform the update.  Updates need to be consolidated back up the chain.

    1.)  Ron receives a spreadsheet from his leader which contains multiple columns (like 40), 5000 rows, and sends it out to his 10 direct reports telling them 'Please have your people update this spreadsheet and return it to me one week from now'.

    2.)  The 10 directs each have 50 people reporting to them and need to update the spreadsheet according to Ron's instructions and return it to him.

    Over the course of the next week Ron receives 10 different emails, some with filtered spreadsheets, some with formatting changes, some with all data removed ... with the exception of what was applicable to the sender.

    What Ron would prefer is if he could use SharePoint to simplify this task and improve the quality of the response data.  Ron's constraints are:

    1.)  Ultimately, Ron will have to send a 'completed' spreadsheet to his leader which consolidates the updates from his directs

    2.)  Ron's directs are geographically in different areas of the globe so Ron can never be sure if 2 or more persons would be editing the spreadsheet at the same time

    3.)  The formatting of the consolidated or final spreadsheet must match the formatting of the original so that Ron's updates can be seamlessly incorporated into an even larger consolidated workbook.

    I'm hoping someone has encountered this issue before ... or has some insight into how SharePoint can simplify this workflow.  Ron's corporation is using SharePoint 2007.

    Thanks in advance to all who respond :)


    • Edited by Steve J0bs Wednesday, January 25, 2012 3:16 PM
    Wednesday, January 25, 2012 3:14 PM

Answers

  • If a SharePoint list were to be created from the spreadsheet and the list updated by these various people then ShareePoint would see to it that only one person at a time amended the contents of that list.

    (In addition as the list is on the web site everyone allowed would be able to see the current state and not the original state.)

    Whether a SP list can be created from the spreadsheet or not will depend on the kinds of fields used (and possibly on the size of it) but it's a quick test to see if creating a list from it is possible or not.

     


    SP 2010 "FAQ" (mainly useful links): http://wssv4faq.mindsharp.com/default.aspx
    WSS3/MOSS FAQ (FAQ and Links) http://wssv3faq.mindsharp.com/default.aspx
    Both also have links to extensive book lists and to (free) on-line chapters
    • Edited by Mike Walsh FIN Wednesday, January 25, 2012 3:29 PM
    • Marked as answer by Pengyu Zhao Thursday, February 2, 2012 2:10 AM
    Wednesday, January 25, 2012 3:28 PM
  • I assume that everyone listed has connectivity and access to the appropriate SharePoint site.  If so, then Transform the spreadsheet into a SharePoint list and let people edit the records directly in the list.

    If the data MUST end up in an .xls(x) file, perhaps due to formatting or complex calcs, then you can always export it and apply the needful.

     


    Steve Clark, MCTS | Twin-Soft.com
    • Marked as answer by Pengyu Zhao Thursday, February 2, 2012 2:10 AM
    Wednesday, January 25, 2012 3:32 PM
  • If the forums we're more like SharePoint, then Mike could have 'Checked Out' the record to prevent me from answering while he was answering. :D

    SharePoint lists have a setting called 'Item-Level Security' of which you can restrict a user to "Only see their own."  This only works well if the user creates the record, but would not satisfy your need for the name listed in a column.

    There is a way, using some codeplex SPD Utilities Actions to dynamically alter the permissions for the record, so I'll say that the list is still the better method than a swamp of spreadsheets.


    Steve Clark, MCTS | Twin-Soft.com
    • Marked as answer by Pengyu Zhao Thursday, February 2, 2012 2:11 AM
    Thursday, January 26, 2012 4:56 PM

All replies

  • If a SharePoint list were to be created from the spreadsheet and the list updated by these various people then ShareePoint would see to it that only one person at a time amended the contents of that list.

    (In addition as the list is on the web site everyone allowed would be able to see the current state and not the original state.)

    Whether a SP list can be created from the spreadsheet or not will depend on the kinds of fields used (and possibly on the size of it) but it's a quick test to see if creating a list from it is possible or not.

     


    SP 2010 "FAQ" (mainly useful links): http://wssv4faq.mindsharp.com/default.aspx
    WSS3/MOSS FAQ (FAQ and Links) http://wssv3faq.mindsharp.com/default.aspx
    Both also have links to extensive book lists and to (free) on-line chapters
    • Edited by Mike Walsh FIN Wednesday, January 25, 2012 3:29 PM
    • Marked as answer by Pengyu Zhao Thursday, February 2, 2012 2:10 AM
    Wednesday, January 25, 2012 3:28 PM
  • I assume that everyone listed has connectivity and access to the appropriate SharePoint site.  If so, then Transform the spreadsheet into a SharePoint list and let people edit the records directly in the list.

    If the data MUST end up in an .xls(x) file, perhaps due to formatting or complex calcs, then you can always export it and apply the needful.

     


    Steve Clark, MCTS | Twin-Soft.com
    • Marked as answer by Pengyu Zhao Thursday, February 2, 2012 2:10 AM
    Wednesday, January 25, 2012 3:32 PM
  • Thank you very much for the reply ... 2 follow up questions for you:

    1.)  If I place a Spreadsheet on my SharePoint ... then create a list based on the Spreadsheet (now located in the SharePoint), and multiple persons update data in the list ... will those changes also be reflected in the Spreadsheet?

    2.)  Is there a way to restrict the permissions on the list so that only certain rows can be edited based on User Name data found in one of the columns?

    One of the business concerns is that the original spreadsheet (Sent to Ron in the example above) and the updated version of the spreadsheet are essentiall the same in terms of formatting look and feel.

    • Edited by Steve J0bs Wednesday, January 25, 2012 3:40 PM
    Wednesday, January 25, 2012 3:38 PM
  • 1. No. The whole point is that the SharePoint list *replaces* the spreadsheet.

    2. can be picked up by Steve! (who can write at the same time as me again ..)


    SP 2010 "FAQ" (mainly useful links): http://wssv4faq.mindsharp.com/default.aspx
    WSS3/MOSS FAQ (FAQ and Links) http://wssv3faq.mindsharp.com/default.aspx
    Both also have links to extensive book lists and to (free) on-line chapters
    Wednesday, January 25, 2012 4:04 PM
  • Thank you both.

    Its amazing you know ... for all the great things we can do with SharePoint ... getting something which fits a certain workflow can really take some further understanding and doing.

    Wednesday, January 25, 2012 4:43 PM
  • If the forums we're more like SharePoint, then Mike could have 'Checked Out' the record to prevent me from answering while he was answering. :D

    SharePoint lists have a setting called 'Item-Level Security' of which you can restrict a user to "Only see their own."  This only works well if the user creates the record, but would not satisfy your need for the name listed in a column.

    There is a way, using some codeplex SPD Utilities Actions to dynamically alter the permissions for the record, so I'll say that the list is still the better method than a swamp of spreadsheets.


    Steve Clark, MCTS | Twin-Soft.com
    • Marked as answer by Pengyu Zhao Thursday, February 2, 2012 2:11 AM
    Thursday, January 26, 2012 4:56 PM