1

I have been assigned to create a SharePoint for a business with multiple departments. So far it it has gone well but one of the requirements was a dashboard like tool that automatically updates daily with some of the most important numbers for the day. The dashboard would pull information from excel sheets on a local server. I have done a good bit of searching and have not found anything that would be able to do this. Is it even possible to do? If so could you explain, thanks. I know this is brief so if there are any questions let me know.

  • Indeed some clarification is needed. Are the Excel files hosted in SharePoint, or just some other host at the same domain? Could they be hosted at SharePoint (by e.g. replicating)? SharePoint offers built-in web parts for embedding Excel in several ways, so it's definitely doable. – moe Jun 28 '17 at 19:13
  • Yes, I could pull data from the sharepoint saved files or from a internal server. They can be replicated. would you know the name of these web parts? – Carmen Jun 28 '17 at 19:44
  • Carmen that would be Excel Web Access Web Part. Please refer to this to get an overview of it: Display Excel content in an Excel Web Access Web Part. – moe Jun 28 '17 at 19:48
  • So, I have the excel work book embedded, but i only want pieces of the workbook showing because its a very long excel sheet and those pieces are in different spots. Is it possible to just show pieces of it. Also, the workbook changes title every week (week number changes at end of title ie weekly report 22 to weekly report 23) is there any way to have this automatically pull excel data from a new report every week. I am thinking this task of requirements are impossible. – Carmen Jun 29 '17 at 14:50
  • I've extended my answer below, so please go through it once more. If there are any questions remaining, please address them at the comment-section of the answer! – moe Jun 29 '17 at 17:32
0

You could synchronize a SharePoint list with the excel spreadsheet. This would allow you to edit the excel sheet which would update a list on the site. You could then make the dashboard using information with the list, which will sync with any changes to the excel sheet. You can find information on syncing the list and excel sheet here.

  • Yeah that is what my initial thought was but what dashboard would you use? Im assuming there is an add on web part that works pretty good. Nothing fancy just need like simple line of information like daily sales xxxxx yesterday sales xxxxx safe days xxxx etc – Carmen Jun 28 '17 at 19:46
  • I'm not sure on what dashboard to use. I have never made one myself. However, here is some more info that may help you. – belmer1416 Jun 28 '17 at 19:59
0

By using Excel Web Access Web Part you can embed complete Excel workbooks, or its parts including sheets, named areas and named items such as figures and tables.

If you need to do visualization or show some graph, you should have it done in Excel as a figure, name the figure, and have it called by your Excel Web Access Web Part. The naming will remain although the data inside the area would be updated. A named area is then defined within the SharePoint inside the web part's properties:

(This is optional.) To display a single item, such as a chart, table, PivotChart report, or PivotTable report, in the Named Item box, type the name of the item. This causes only that item to be displayed in the Web Part, instead of displaying the entire workbook.

If your requirement is to show several named areas of the workbook, then you'll also need several Excel Web Access Web Parts, as each web part may show only one (named) area at once.

Read more about Display Excel content in an Excel Web Access Web Part. Also a related read of naming e.g. tables inside Excel: Rename an Excel table.

When you are defining the Excel file to be used in the web part, there might be issues when calling a file outside the SharePoint. There are many scenarios where issues might occur, so I'll not get into that. Also the referencing might work without any issues. Talking from own experience, I've sometimes had to implement a replication for the Excel file in order to get it into SharePoint's environment to trivialize the referencing.

As you added in a comment above, your current way of handling the Excels involves a naming convention of adding the ongoing weekly number to the end of the file name. This would cause the Excel to not update in your web access web part. I had a similar issue in one customer case, where we as a workaround agreed to instead name each week's file with the same file name as the previous ones (i.e. without the unique number). Then by using SharePoint's file versioning, which can be enabled in the library the document is hosted in, the Excels of the previous week could be stored as past versions. This had its additional benefits, as the history remained accessible within SharePoint, and no manual file naming was required.

  • Good stuff ill try to implement it tomorrow thanks for the responses. and ill let you know if i run into any problems. – Carmen Jun 29 '17 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.