• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can I use a list from Name Manager in a CUBEVALUE formula?

Christof

Member
Hi

I am trying to put a formula together that will sum up a Total of Ancillary Revenue based on a List.
Now I could do it by adding up all the values in the cells like my code below (where my list of possible values is in Cell $S$61 downwards......

Code:
=IFERROR(CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Ancillary Revenue]","[AncillaryInfoTable].[Ancillary Description].&["&$S61&"]",Slicers1),0)
+IFERROR(CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Ancillary Revenue]","[AncillaryInfoTable].[Ancillary Description].&["&$S62&"]",Slicers1),0)

.....but was wondering if it's possible to reference a Named List in the formula instead of the static cell references, as there may be as many as 8 values. Something like this..

Code:
=IFERROR(CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Ancillary Revenue]","[AncillaryInfoTable].[Ancillary Description].&["&NamedList1&"]",Slicers1),0)
Any ideas?

Thanks
Chris
 

GraH - Guido

Well-Known Member
I've only started to show an interest for these functions myself since a few weeks. So first thought was to say "I'm afraid not. Since your 'list name' is not a cube member, only each value is. As far as I know , cube functions only work with cube dimensions and cube measures."
However Cubeset() - which I've never used - help seems to suggest otherwise.

Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Excel.
CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
The CUBESET function syntax has the following arguments:
  • Connection Required. A text string of the name of the connection to the cube.
  • Set_expression Required. A text string of a set expression that results in a set of members or tuples. Set_expression can also be a cell reference to an Excel range that contains one or more members, tuples, or sets included in the set.
  • Caption Optional. A text string that is displayed in the cell instead of the caption, if one is defined, from the cube.
  • Sort_order Optional. The type of sort, if any, to perform and can be one of the following:
 
Top