XFGetCell for multiple members

[OSAdmin]

Originally posted by Brad Kotecki

Is there a way, or a best practice recommended, to point an XFGetCell formula to one or multiple members of the same dimension without adding parent level members in the dimension hierarchy?

As an example, if we have entities 1 through 5, I'd like to be able to change an input cell to any combination of those and have an XFGetCell function to pull in the sum of all the data points referenced. Currently I'm thinking this would require nested If functions, but that seems like it would quickly get to an unmanageable length as the number of members grows.

Thanks

Brad

[OSAdmin]

Originally posted by Colin ONeill

Here is an Excel solution:

Have five data pull sheets:

The rows/columns of these sheets are filled with the parameters for the XFGetCell formulas found in the cell intersections. Each of these sheets is identical, except for the entity.

Have a summary sheet:

Put five Include/Exclude cells in the header of the summary sheet, one for each entity. Fill these cells with a one to include and a zero to exclude.

The rest of the summary sheet looks exactly like the data pull sheets. Except there are are no XFGetCell formulas, instead there is a formula in each of the data cells multiplying the Entity 1 value by the Include/Exclude 1 value plus the Entity 2 value by the Include/Exclude 2 value, and so on.

This would be simple to admin.

To add a new row or column, add it to all 6 sheets (data x5 + summary).

To add a new entity, add a new data sheet, add a Include/Exclude cell on the summary sheet and make a sweeping change to all of the identical formulas on the summary sheet.