PDA

View Full Version : Excel 2010 ... Connection Changed from "PowerPivot Data" to "ThisWorkbookDataModel"



Ted Murphy
2016-05-13, 02:05 AM
Earlier this week I had cause to Download Excel 2010 from the MSDN Site and re-install it.

Suddenly the following Function call ="Fiscal Year: "&CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear4,1) started returning a #NAME error

Once I changed to connection to "ThisWorkbookDataModel" e.g ="Fiscal Year: " &CUBERANKEDMEMBER("ThisWorkbookDataModel",Slicer_FiscalYear4,1)
it returns the following:

Fiscal Year: 2016



Apart from having to edit a ton of individual Cell values ... my concern lies with what will happen when I distribute the Workbook to my client ... who will not have updated his copy of Excel 2010.

Anybody know when this change came about?

What is the best approach to take?

Thanks,

Ted

Ken Puls
2016-05-24, 07:29 PM
Ted, you hit me with a new one here. I thought that "PowerPivot Data" was 2010, and "ThisWorkbookDataModel" was 2013. You're actually seeing "ThisWorkbookDataModel" working in 2010?

One method that Bob Phillips suggested in the past was to create a named range (let's call is "MyCube"). MyCube would then be the correct version for the workbook. This, at least, would allow a very easy conversion, maybe even via a drop down list on a control panel worksheet. I'm not sure if I'm a fan of this, but you might even be able to set this up:

-Create a CUBE formula that pulls something that will never fail, with the ThisWorkbook string.
-Create an IFERROR statement to test it, and return "PowerPivotData" if it does fail, or ThisWorkbook if not
-Have the named range point to that cell

I haven't tested this, and it would add extra overhead, but may solve the issue.