excel cell reference in formula

Topics: Resolved
Nov 3, 2009 at 8:16 PM
Edited Nov 3, 2009 at 8:21 PM

is it possible to pass values into the mdx formula from an excel worksheet? so for example, in cell A1 i have a date, can i then have an mdx formula that gets its value from this cell (like [Time].[As of Date].Range("A1") )? Thanks

Coordinator
Nov 5, 2009 at 7:03 AM

Unfortunately not. One of the design principles for OLAP PivotTable Extensions is to not require consumers of PivotTables to have to have the add-in, just the designers of PivotTables. So I would have had to build that "cell reference" stuff into the add-in, then it wouldn't work without the add-in. Make sense?

What you probably want to do is use Cube functions in Excel like =CubeValue. Since it's just an Excel formula, it could be tied to a cell reference. An easy way to get a start is to convert your PivotTable to formulas. (That's an option under the OLAP Tools dropdown. But unfortunately any calculations you've created with OLAP PivotTable Extensions will be lost during this conversion. It's a known bug in Excel.)

Marked as answer by furmangg on 6/10/2014 at 3:43 PM
Coordinator
Nov 7, 2009 at 4:14 PM

I had one other idea that would get you into the ballpark of what you're asking for, but I forgot to post. I would require working with the developer of your cube (which may or may not be appropriate for you or your scenario). But Chris Webb describes the idea of using a dimension to control a parameter to a calculation:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!5089.entry

Marked as answer by furmangg on 6/10/2014 at 3:43 PM
Jan 15, 2010 at 7:32 PM
Edited Jan 15, 2010 at 7:32 PM

Yes, it IS possible

Like this:

[Masterdate].[Year].[All Date].[" & H1 & "]

where the value is in the cell H1, since i have 2010 in that cell it returns for current year

 

*registered just to tell you this because it tooke me a while to figure out myself

Coordinator
Jan 18, 2010 at 2:06 PM

Gaujo-

Thank you so much for taking the time to register in order to tell us about your suggestion.

However, I cannot seem to understand the exact steps you used in order to accomplish what you're describing. Where are you entereing that formula? Are you entering it into OLAP PivotTable Extensions? Or are you entering it into an Excel formula in a cell? Is there a PivotTable involved, or have you converted the PivotTable to formulas?