Offset,Indirect Please help

[ciara_daniels@yahoo.com]

I cant seem to get this to work. It's giving me the wrong answers and I

cant work out what it's doing wrong.

I have used formula
SUM(OFFSET(INDIRECT("E"&MATCH(C16,'Targets KPI
sheet'!$A$7:$A$110,0)),0,0,1,'Front Sheet KPI Statistics'!$G$5))


"E" is the column on the budget sheet where the monthly data starts
$g$5 contains the number of months on the summary sheet
c16 contains the "kpi A"
E16 is where I've entered the formula on the summary sheet
$A$7:$A$110 is the list of the KPIs


Is it obvious what Im doing wrong? The result should be 20 for month 10

as I have the value "2" in columns 1-10. I keep getting 15.

MY ORIGINAL POST BELOW
Hi
I have a budget sheet with the following

KPI Apr May Jun Jul
KPI A 20 21 25 27
KPI B 44 22 33 22


I then have a summary sheet with the following
Report Month : (user enters report month number)


KPI ***. YTD Target ***. Actual Annual
Target
KPI A (formula A) (formula B)
(formula C)
KPI B (formula A) (formula B)
(formula C)


What I need is formula A.I would like to be able to get the cumulative
YTD total based on the report month entered by the user.
For example if the user enters 2 as the report month, then the *** YTD
total for KPI A should show as 20+21=41.


How can I achieve this? I have thought of using sumif, but I cant work
out how to combine it with a lookup or maybe there is a better way to
achieve the result.



TIA

[Biff]

Hi!

There's nothing wrong with your formula so it's hard to say what the problem
might be.

Maybe you need to offset by 1 column or you need to adjust the MATCH value
(+/-1) ???

Biff

<ciara_daniels@yahoo.com> wrote in message
news:1156194585.696273.148980@h48g2000cwc.googlegroups.com...
>I cant seem to get this to work. It's giving me the wrong answers and I
>
> cant work out what it's doing wrong.
>
> I have used formula
> SUM(OFFSET(INDIRECT("E"&MATCH(C16,'Targets KPI
> sheet'!$A$7:$A$110,0)),0,0,1,'Front Sheet KPI Statistics'!$G$5))
>
>
> "E" is the column on the budget sheet where the monthly data starts
> $g$5 contains the number of months on the summary sheet
> c16 contains the "kpi A"
> E16 is where I've entered the formula on the summary sheet
> $A$7:$A$110 is the list of the KPIs
>
>
> Is it obvious what Im doing wrong? The result should be 20 for month 10
>
> as I have the value "2" in columns 1-10. I keep getting 15.
>
> MY ORIGINAL POST BELOW
> Hi
> I have a budget sheet with the following
>
> KPI Apr May Jun Jul
> KPI A 20 21 25 27
> KPI B 44 22 33 22
>
>
> I then have a summary sheet with the following
> Report Month : (user enters report month number)
>
>
> KPI ***. YTD Target ***. Actual Annual
> Target
> KPI A (formula A) (formula B)
> (formula C)
> KPI B (formula A) (formula B)
> (formula C)
>
>
> What I need is formula A.I would like to be able to get the cumulative
> YTD total based on the report month entered by the user.
> For example if the user enters 2 as the report month, then the *** YTD
> total for KPI A should show as 20+21=41.
>
>
> How can I achieve this? I have thought of using sumif, but I cant work
> out how to combine it with a lookup or maybe there is a better way to
> achieve the result.
>
>
>
> TIA
>