# Cell References in Cube Formulae

#### ex_cowboy

##### New Member
Hi,

I'm looking to run a CUBEVALUE formula with member expressions based on the contents of multiple different cells. For this example i have 2 cells with a value for REGION in:

I15: Border
I16: Midlands

I can reference one cell successfully using a cube value formula:
=CUBEVALUE("PowerPivot Data","[Measures].[Sum of WEIGHTED_IMPRESSIONS]","[pvtBASE].[REGION].&["&I\$15&"]")

Couldn't find a way within CUBEVALUE alone to replicate this result to reference both I15 and I16 so tried with a CUBESET then referencing the CUBESET in a later CUBEVALUE formula:

FOr the CUBESET, this formula works:

=CUBESET("PowerPivot Data",{"[pvtBASE].[REGION].&[Midlands]","[pvtBASE].[REGION].&[Border]"})

This formula works:

=CUBESET("PowerPivot Data","[pvtBASE].[REGION].&["&I15&"]")

But for some reason this doesn't:

=CUBESET("PowerPivot Data",{"[pvtBASE].[REGION].&["&I15&"]","[pvtBASE].[REGION].&["&I16&"]"})

Does anyone know how to fix the final CUBESET formula or if perhaps there is another way of fitting multiple members and cell references into a CUBEVALUE formula.

Feels like i'm close but then again I might not be!

Cheers

Rab

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Tianbas

##### Board Regular
try =CUBESET("PowerPivot Data";I15:I16)

#### Tianbas

##### Board Regular
maybe better use =CUBESET("PowerPivot Data";I15:I16;"My Region Set") otherwise you might not "see" the cell even if its working in a Cubevalue formula

#### ex_cowboy

##### New Member
try =CUBESET("PowerPivot Data";I15:I16)

Thanks for this. This does work for that example actually, however I'm looking to include a number of other fields on top of REGION. A number of these fields will share values in particular alot of them would be Yes/No or numbers, so i need to still reference the header somehow. i.e:

 REGION HAS_KIDS HAS_CAR SEGMENT AREA_CODE Border Yes Yes 02 01 Midlands 03 03

<tbody>
</tbody>

Ideally i'd be looking at a CUBESET formula that could capture - REGION: Border or Midlands, HAS_KIDS: Yes, HAS_CAR: No, SEGMENT: 02, 03, AREA_CODE, 01,03 and based on what is entered in cells on a table like above.

#### Tianbas

##### Board Regular
not in one set but you can use more than one set in a CUBEVALUE so just build your 5 sets and include the Cells in your CUBEVALUE formula =CUBEVALUE("Powerpivot Data";A1;A2;A3,...) if A1 includes the CUBESET for region, A2 for Has-Kids and so on

#### ex_cowboy

##### New Member
not in one set but you can use more than one set in a CUBEVALUE so just build your 5 sets and include the Cells in your CUBEVALUE formula =CUBEVALUE("Powerpivot Data";A1;A2;A3,...) if A1 includes the CUBESET for region, A2 for Has-Kids and so on

Thanks for your help again. Just without the header referenced in some way in the CUBESET, the CUBEVALUE would essentially be referencing identical CUBESETs i.e

CUBEVALUE("Powerpivot Data"; CUBESET or Border, Midlands, CUBESET of 'Yes', CUBESET of 'Yes'...)

If that makes sense?

#### Tianbas

##### Board Regular
If the name is uniqe like border or midlands the set is working just with the name. For the other Sets the different reference cells might need to be CUBEMEMBER formulas.

#### ex_cowboy

##### New Member
Brilliant, got it working now using the CUBEMEMBERS in the CUBESET as you recommended.

Thanks again.

Replies
0
Views
410
Replies
1
Views
1K
Replies
0
Views
1K
Replies
1
Views
454
Replies
2
Views
1K

1,190,957
Messages
5,983,835
Members
439,864
Latest member
qazxsw12

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back