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.