0

I have 20 people that I need to collect data from. The number of questions they need to answer is around 100, and each answer needs to be analyzed after they all submit their responses before we move onto the next question (all answers are numerical, and data is being collected real time).

Is it possible to have an Excel file or Access file on SharePoint, that can be edited simultaneously by multiple people? I am currently using just a List on SharePoint, with a custom form in order to accept their inputs. After they submit the form, I update the exported Excel workbook built from the List, and analyze the answers. While this works, the issue is the time and hassle of my participants constantly reopening the form, answering, then submitting (also reloading the form and refreshing web pages is not liked by the crappy network we run on). I could skip the custom form and have them input directly into the list, but the custom form has images and data validation that is being used to limit data entry error and improve clarity of the questions.

I need to streamline the process (also I only have access to Microsoft Office products). What would be ideal is if they all had a custom Excel workbook opened on their end, and when they hit save (or a submit button that makes the doc save) I see the updates on my end. SharePoint seems to be the only medium for me to host this data exchange on, I just can't figure out how to make it happen. If it is even possible.

Note: Assume I do not have the ability to use Web Parts due to some features of SharePoint being locked down because of security concerns.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Garrett Alarcon
  • 1

2 Answers2

0

I have used Web Excel for this purpose at work. This allows multiple people to view and edit (given permissions) the Excel spreadsheet. As they navigate to the subsequent row, it autosaves.

This solution will need someone with some knowledge of SharePoint - not necessarily expert, but you should know how to set up a Library and something about permissions as well as perhaps creating up a page and adding links to it. You will not need Web Parts.

  1. Create a Library to hold this spreadsheet (and possibly other future spreadsheets). Choose the Library tab, then click Library Settings: enter image description here

  2. Select Advanced Settings:

enter image description here

  1. Find this option and select to always open in the browser. This defaults to Web Excel for viewing:

enter image description here

  1. Add the spreadsheet to the library (you can drag and drop it).

  2. 2 choices now:

    a. either just send the users the address as shown below from here: enter image description here

    b. create a new page with the link to the spreadsheet. Then in the future if the spreadsheet changes, you just change the link on the page - this is valuable if you want to use different spreadsheets in the future and don't need to send updated links each time.

You will need to give update permissions to the library (or the whole SharePoint site, your choice) to everybody who can update. (I'm not going to cover that but from the Library Settings page, choose Permissions).

Users will be able to view the data then when they want to edit, they pick Edit Workbook and then Edit in Excel Web App:

enter image description here

One caveat - the max spreadsheet size is 10MB. I had a spreadsheet that was 30 MB and couldn't use via Web Excel (see message below) but found out that if I saved it as a binary file (XLSB extension), it reduced it to 3MB - just in case you encounter that problem:

enter image description here

cardmagik
  • 1,698
  • 19
  • 17
  • Thanks! This works for what I'll need. It seems though SharePoint 2010 doesn't support in browser Excel editing? Our organization just started the migration to 2013 where I know it works, but not everyone is there yet. Thanks again! – Garrett Alarcon Oct 03 '17 at 13:39
  • @Garrett_Alarcon Can you mark this as the correct or best answer then please? – cardmagik Oct 29 '17 at 19:25
0

Another option would be if you have SharePoint foundations you could have a form that connects to the list, that way you have ridge and a very simplistic way of people inputting information. You could also create an approvals column where the information will have to be approved, then have a view of all approve data and then on the ribbon select export to excel.

export to excel

Another longer process is use SharePoint's' REST API to datatables to export to excel. I know you said you cannot use webparts but it is a very useful tool to streamline and produce great tables that can then be exported to excel or as a csv, this way you can manage a very large amount of data. I would look into this process if this data will be large and will be going on for a long period of time:

https://datatables.net/

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/complete-basic-operations-using-sharepoint-rest-endpoints

Kay Carosfeild
  • 91
  • 1
  • 1
  • 12