Cell Reference excluding blanks/errors

**rab2a** · 10-02-2014, 11:07 AM

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

=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

=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

**FlameRetired** · 10-02-2014, 12:36 PM

Can you upload a sample file?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

**rab2a** · 10-02-2014, 01:08 PM

Thanks for your reply. I have a solution (not shown in the example) using dynamic ranges which will give me the CUBESETs i require.

Now just need the CUBEVALUE formula to pickup cell references in an array of CUBSETS excluding where N/A.

Attached a file, it'll contain errors and ?NAME where it would be N/A as it's not connected to the data so added a pasted as values tab to show what the sheet would look like; hopefully you should be able to see what i'm after:

Basically I'm looking to run a CUBEVALUE formula on G77:BB77 which will only reference where i don't have errors. My basic attempt at a formula can be seen in H81,H82.