Offset,Indirect Please help
 

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 Cum. YTD Target Cum. 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 cum 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

Offset,Indirect Please help
 

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

wrote in message
oups.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 Cum. YTD Target Cum. 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 cum 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