• 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?

C

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
 
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:

CUBESET function - Microsoft Support

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.
support.office.com support.office.com
 
You must log in or register to reply here.
Back
Top