CUBESET/CUBEVALUE formula reference an array but ignore blanks

[ex_cowboy]

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