CUBESET/CUBEVALUE formula reference an array but ignore blanks

ex_cowboy

New Member
Joined
Feb 17, 2009
Messages
9
Hi

I have a cubeset formula which i want to reference an array however sometimes there will be only one value in the column, sometimes 2, 3.
Manchester
Liverpool

<tbody>
</tbody>

=CUBESET("PowerPivot Data",A1:A6) give an error. Need an expression which will just pick up the first 2 populated cells in this example or do something about the blanks.


Have a similar problem with CUBEVALUE where i want to reference only cells from a row where there isn't an error (or other set value)

#N/A#N/A#N/A#N/A

<tbody>
</tbody>

=CUBEVALUE("PowerPivot Data",
"[Measures].[Sum of WEIGHTED_IMPRESSIONS]",H18:H23) gives an error. Here i'd like the formula to only reference the cells where there isn't an error.

Just wondering if there is a solution to these two problems maybe using INDEX/MATCH or perhaps some workaround?

Thanks

Rab
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,109,521
Messages
5,529,322
Members
409,863
Latest member
stacy09
Top