Did you know that … ?
– references to a cell containing a CUBE function return an OLAP object, just as the source cell;
– range operations on cells containing CUBE formulas return ranges of OLAP objects, that you can use in other CUBE functions;
– a copy & paste as value operation will remove the formula, but the destination cell will contain a new OLAP object containing the original definition.
It means you can:
– use your favorite functions (INDEX, VLOOKUP, …) on ranges of cells containing cube functions and use the result in a CUBEVALUE function;
– let end users choose the sets and measures they want to see with simple data validation lists that use range of cells containing CUBE formulas. You might not even need to use any lookup functions or IF statements nested within a CUBE formula to build your dashboard. Just keep it simple.
The fact that copy & paste as value operations on CUBE formula create OLAP objects may hurt you:
1. The objects remain bound to the source, as can be seen when trying such an operation in another workbook;
2. The objects will be updated with every connection refresh. The fact the target no longer contains any formula, may lead end-users to think they made a backup of earlier values, whereas the result will be updated on every connection;
3. The result of a copy & paste as value operation will retain the original definition. For example, copying a cell containing a CUBESET function will keep its references to the same members even after the original set or cells referenced have been modified. However, if the definition referred to a named set, then the result object will also change when this named set changes.