EPM - Keep named range

[Former Member]

Hi all,

This is my first posts here (and probably the first of a big serie; I'm totally new to SAP EPM).

I'm coding my first EPM report and this report is a mix of BPC data and Excel formulas.

In a given sheet, I've an EPM report to retrieve year figures; months by month.   I've one column by month.

To easily work with this report with "SUMIF" Excel function, I've named each column (_rngJanuary, _rngFebruary, _rngMarch and so on).  Everything is working fine except when I use the EPM context and select an another year : my names are still there but no more reflecting the correct range but only the two last cells of the columns (???).   The original _rngJanuary range was f.i. C2:C201 and, after selecting an another year, the range for _rngJanuary become C200:C201 (second screen capture)

My question : how to tell EPM to keep my names and their exact location ?  

Hope I am clear enough...

Thanks a lot !

[Shrikant_Jadhav · Accepted solution]

Hi Christophe,

try this

Options -> Sheet options -> Refresh -> Keep Formulas Static that References Report Cells.

(It might impact behavior of other function if you excel references)

Shrikant

[Former Member]

Thank you Shrikant, you got the point 😉   Indeed, I've tried a few others options but not this one.

Thanks a lot.

[Former Member]

Hi Christophe,

You have to use the offset formula to create a dynamic range.

In the name manager define the range name using a formula like this:

rngJan =OFFSET(C1,1,0,COUNTA(A:A),1)

rngFeb =OFFSET(D1,1,0,COUNTA(A:A),1)

.....

Hope this helps

Rene Moreno