Cell Refrence in Cubemember Formula

[bjurney]

I have a Cubemember formula that looks like the following:

=CUBEMEMBER("Cube Connection",{"[Sales Agent1].[Agent Name].&[LAST NAME, FIRST NAME]","[Source].[Source Name].[All]"})

But what I would like to do is reference the name of the agent to a cell to look like this

=CUBEMEMBER("Cube Connection",{"[Sales Agent1].[Agent Name].&["&A8&"]","[Source].[Source Name].[All]"})

No matter what variation I try, nothing seems to work to get a cell reference working. I tired a CUBEVALUE formula, but it returns a number and not a name like I would like. Any thoughts or suggestions would be appreciated.

 

[bjurney]

I figured out a work around by putting the expression in another cell and referencing it, seems a little silly that I cant nest the text in the formula itself though.