1

I want to create a dashboard in Sharepoint using data from Excel Workbooks. Each Workbook has 2 sheets: Data and Dashboard. The Data worksheet contains raw data and the Dashboard tab contains KPI's calculated from the raw data.

We have users entering the raw data, so there is data validation built into some of the cells in the Data worksheet. Both sheets have Protect Sheet turned on with a password.

If I create a page with Excel Web Access built into it, the Excel file will not display and I get an error - understandable.

Question is: the copy of the Excel file in Sharepoint is not the document of record, so we don't care if Data Validation and Sheet Protection are stripped from them. Is there a way to automate that process? In other words, when an Excel file is posted to the document library, Data Val and Sheet Prot are turned off so that the file can be displayed in the Web Part of a page?

If not, is there an elegent way around the situation? Thanks.

Improve this question

1 Answer 1

Reset to default
1

What about using Excel REST to pull the content from your worksheet? If you create a chart then it will just come back as an image. In case you need it there is an intro to Excel REST on my blog http://paylord.wordpress.com/2013/04/20/excel-rest-meets-xsl/

Improve this answer
3
  • REST looks interesting on first read - will have to dig into it more. Is REST native to Sharepoint or is it an add-on of some kind? I'm using a hosted version of Sharepoint Enterprise so there may be limitations to what I can add. Also, can the process be automated? The folks filling out the data in Excel and uploading the files are not sophisticated and we want to keep their tasks as straightforward as possible.
    – andrew
    Oct 25, 2013 at 12:22
  • OK, just read your blog and some other documentation more thoroughly - please ignore the above questions. I may come back to you with more as I get into implementation. Many thankss
    – andrew
    Oct 25, 2013 at 12:52
  • Great - good luck
    – Dave Paylor
    Oct 25, 2013 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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