How to reference a range of cells containing monthnameshort in GETPIVOTDATA

[sko]

I am trying to build a more agile and time limiting financial reporting tool. In this proces I stumpled upon a problem that I can't seem to fix.

I am trying to get Year-to-date numbers by referencing a range of cells containing Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep

Formula that works for january only:

=GETPIVOTDATA("[Measures].[Amount]";'Regnskab pr. mdr.'!$A$10;"[Dim GL Account Hierachy].[Parent GL Account]";"[Dim GL Account Hierachy].[Parent GL Account].&[1495 Nettoomsætning i alt]";"[Dim Posting Date].[MonthNameShort]";"[Dim Posting Date].[MonthNameShort].&[Jan]")

Here I want to refence a range of cells containing the months I want (jan to sep). I can't do it this simple:

=GETPIVOTDATA("[Measures].[Amount]";'Regnskab pr. mdr.'!$A$10;"[Dim GL Account Hierachy].[Parent GL Account]";"[Dim GL Account Hierachy].[Parent GL Account].&[1495 Nettoomsætning i alt]";"[Dim Posting Date].[MonthNameShort]";"[Dim Posting Date].[MonthNameShort].&[Jan]:[Sep]")

Alternatively I want to use a cell-range in the formula containing jan to sep, like sheet1!A1:A9

Does anybody know how to make this work?

Thanks a lot

 

[RoryA]

I suspect you'll need something like:

=SUM(GETPIVOTDATA("[Measures].[Amount]";'Regnskab pr. mdr.'!$A$10;"[Dim GL Account Hierachy].[Parent GL Account]";"[Dim GL Account Hierachy].[Parent GL Account].&[1495 Nettoomsætning i alt]";"[Dim Posting Date].[MonthNameShort]";"[Dim Posting Date].[MonthNameShort].&["&sheet1!A1:A9&"]"))

If any of the months might be missing, you'll need to wrap the inner GETPIVOTDATA in an IFERROR function too.

 

[sko]

Unfortunately it creates the #SPILL!

=GETPIVOTDATA("[Measures].[Amount]";'Regnskab pr. mdr.'!$A$10;"[Dim GL Account Hierachy].[Parent GL Account]";"[Dim GL Account Hierachy].[Parent GL Account].&[1495 Nettoomsætning i alt]";"[Dim Posting Date].[MonthNameShort]";"[Dim Posting Date].[MonthNameShort].&["&'Regnskab pr. mdr.'!D11:L11&"]")

And when I use the CTRL+SHIFT+ENTER it only grabs the value for Jan, and not the other months in the array.

Hmm.. Do you know if there is another way to grab the values from Jan to Sep in the GETPIVOTDATA above?

 

[RoryA]

That's not the formula I posted - you are missing the SUM function.

 

[sko]

That's not the formula I posted - you are missing the SUM function.

Oh year I see now. It works perfectly,

Thanks a lot!

 

[RoryA]

Glad we could help.