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. Commented 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 Commented Oct 25, 2013 at 12:52
  • Great - good luck Commented Oct 25, 2013 at 21:19

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.