How to make a cell reference inside of array comming from Data Model?

[subzero_dm]

Hello there!

It is my first post in the forum. I am getting crazy with a problem I am having since 2 days and I have no idea how to solve it. I am doing an excel database for my employer, so I am working with related tables and Data Model. For many of the reports I am creating pivot tables work well, but now that I need to go deep in the information I have to extract from the data model and I am getting stucked with CUBE functions when they include arrays inside. When I explained this to a colleague he told me about this forum, apparently people here enjoy solving big excel problems so I think you have an interesting challenge for those who know how to face it.

I have this formula:

Please Login or Register  to view this content.

and if I want to load dinamically the part "Book 1" from cell "G12" I can do it easily like this:

Please Login or Register  to view this content.

The problem comes when I try to do the same in this formula that contains an array:

Please Login or Register  to view this content.

If I try to load "Book 1" also from a cell in the same way done before it is not working:

Please Login or Register  to view this content.

So I have tried in every single way it comes to my mind, like spliting the array or using INDIRECT but nothing, it is impossible. I am attaching a basic example so I will appreciate if in this file someone here is able to make the array reference working.

Thank you in advance!

[Bo_Ry]

Please try

=CUBEMEMBER("ThisWorkbookDataModel","[Books]."&{"[Title]","[Editor]"}&".&["&REPT(G12,{1,0})&REPT("Editor 1",{0,1})&"]")

[subzero_dm]

My colleague was totally right about this forum...

Thank you so much Bo_Ry! I have already download the file and it is working like a champ in my computer, you made my day man!