Cell reference in CubeMember function

[cocochoff]

Hi,
I would like to include cell references into the CubeMember function.

When I use the “Convert to formulas” function in a pivot table linked via Analysis Services I get a CubeMember function like:

CUBEMEMBER("globl0720 KPI DW KPI";{"[KPI].[KPI].[KPI].&[64]"\"[Location].[Blade Type].&[ES-PON]&[1]"})

Since “ES-PON” represents my factory location and I want to fetch the CubeMember from a (dynamic) list of locations such as ES-PON, ES-GAL, DK-VIN etc. I would like to make a cell reference instead.

Example using the data in cell A1:

="CUBEMEMBER("&"""globl0720 KPI DW KPI"&""";{"&{"""[KPI].[KPI].[KPI].&[64]""\""[Location].[Blade Type].&["}&$A$1&"]&[1]""})"

Even building the formula string inside the “Indirect Formula” does not work but returns the result #REF!

I appreciate your help

 

[Andrew Poulsom]

Welcome to MrExcel.

I've never used the CUBEMEMBER function so I'm working in the dark here.

In your formula, the second argument {"[KPI].[KPI].[KPI].&[64]"\"[Location].[Blade Type].&[ES-PON]&[1]"} is an array constant, the backslash being the row separator in your locale (rather than the semicolon). And you can't use cell references to build an array constant. However, Help on the CUBEMEMBER function states that the argument can be a cell range or an array constant. So in B1 enter [KPI].[KPI].[KPI].&[64] and in B2 enter ="[Location].[Blade Type].&["&A1&"]&[1]". Then try the formula:

=CUBEMEMBER("globl0720 KPI DW KPI";B1:B2)

By the way, if you crosspost please say so and provide a link:

Cell reference in CubeMember function

 

[cocochoff]

Thank you so much Andrew. It was really helpful and i appriciate it.

Your altruism level and Excel knowledge are awsome.

Thank you again.

 

[cocochoff]