Cubeset with multiple Excel cell inputs

[NumeritasMartin]

Good Afternoon All,

I hope the festive December month is treating you well.

I am trying to get my Cubeset to accept multiple Cell entries, I have got two working but can't find or figure out the syntax for multiple.

This is what I am currently using;

=CUBESET("ThisWorkbookDataModel","[Department].[Department Group].[All].["&F4&"]:[Department].[Department Group].[All].["&F5&"]","Departments")

I also expect this is returning all Departments (not sure of the order) all departments between the two cells.

Ideally I would like to be able to populate multiple cells and they will all be incorporated into my cubeset. Example below is that all three departments will be included in my cubeset, but not cause an error if blank.

A1	Admin
A2	Marketing Admin
A3	Sales

Thanks

Martin

[v-juanli-msft]

Hi @NumeritasMartin

According to the CUBESET function syntax:

CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

To filter with mutiple criteria, you need to nest a function in the 'set_expression" part.

Reference: CUBESET() function in Excel with Multiple criteria

Best Regards

Maggie

[NumeritasMartin]

Thanks for the reply @v-juanli-msft

I that website before but I am struggling to apply it, I can't see where the multiple cells are referenced and I don't understand how the syntax for the 'Exists' function works, I was kind of hoping for an example or a resource which I can learn about how I can apply multiple criteria to the Cubeset Expression.

It is probably worth mentioning that I want to return multiple results from the same column (i.e. Dept 1 and Dept 2 and Dept 3, from the Department Code field).

I'll keep looking to see if I can figure it out.

[NumeritasMartin]

So by converting the a PIVOT table that I got the following which works unless the cell is Blank.

=CUBESET("ThisWorkbookDataModel","{[Department].[Department Group].&["&$F$4&"],[Department].[Department Group].&["&$F$5&"],[Department].[Department Group].&["&$F$6&"]}","(Multiple Items)")

This is a workable solution for me however I would like to have a error wrapper for blank cells.

Thanks

[v-juanli-msft]

Hi @NumeritasMartin

If the blank value throws and error after using your formula, you could use "IFERROR" to handle it.

Reference:

Excel IFERROR with VLOOKUP - elegant way to trap errors

if you want the formula to ignore the blank value, you could use "ISBLANK" 

Reference:

ISBLANK(value)

Using IF to check if a cell is blank

Best Regards

Maggie