I have an excel spreadsheet that returns data based on a cell value. In essence, it's
Select * from table where field = Sheet2!A1.
All works like expected, except when I publish it to SharePoint, where I'm told that "PivotTable reports that are set to refresh when the item in a report filter changes" are unsupported.
So I get that SP doesnt support this feature, as the error message cleverly explains. I've seen rumblings that ExcelREST services help work around this issue, but cant find anything specific. Can anyone point me in the right direction on this? Ultimately, I want the user to be able to supply a value, and have the spreadsheet perform it's queries based on that value.
EDIT: Seeing some postings that a User Defined Function might get me over the hump, can anyone speak to that?
EDIT2:OK, lets try it this way. If I define my cell as a parameter, is there any way I can have the user enter that value via the "sharepoint gray bar", and then have them "refresh" the data in the worksheet, pulling relevant data based on the parameter value?