Doing a sum in way of Vlookup???
 

Hi,

I need to do MAT (Moving Annual Totals) on some employees performance on a
12 monthly basis. In one sheet I have data for these employees and in another
sheet I need to do MAT (rotating 12 month sums). Problem is in the first
sheet the data keeps changing (updated monthly) so I need to do a vlookup to
look up the information for any particular employee. Is there a function that
does this???

Thanks for your help in advance.

Bala

Doing a sum in way of Vlookup???
 

Hi Bala,

You should think about using Pivot Tables for your information. Depending
on the size of your application, maybe even an Access database where you can
build this kind of functionality natively.

HTH

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"



"Bala" wrote in message
...
Hi,

I need to do MAT (Moving Annual Totals) on some employees performance on a
12 monthly basis. In one sheet I have data for these employees and in
another
sheet I need to do MAT (rotating 12 month sums). Problem is in the first
sheet the data keeps changing (updated monthly) so I need to do a vlookup
to
look up the information for any particular employee. Is there a function
that
does this???

Thanks for your help in advance.

Bala

Doing a sum in way of Vlookup???
 

Hi Bala,

Assuming that the months are columns and the rows are employees and named
range ThisMonth contains the current cumulative month number, and the first
column of data is the employee number, and the first row of data is the
month,
try using MATCH to find the row number for the employee and OFFSET to get
the 12 months you need, something like this:

SUM(OFFSET(DataSheet!$A$1,MATCH(EmpNum,DataSheet!$ A$1:$A$20000,0),ThisMonth,1,12))


If you need a weighted moving average you will need to use SUMPRODUCT to
multiple the months by the appropriate weight instead of SUM.

regards

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Bala" wrote in message
...
Hi,

I need to do MAT (Moving Annual Totals) on some employees performance on a
12 monthly basis. In one sheet I have data for these employees and in
another
sheet I need to do MAT (rotating 12 month sums). Problem is in the first
sheet the data keeps changing (updated monthly) so I need to do a vlookup
to
look up the information for any particular employee. Is there a function
that
does this???

Thanks for your help in advance.

Bala

Doing a sum in way of Vlookup???
 

Thanks Zack & Charles. Appreciated.

"Charles Williams" wrote:

Hi Bala,

Assuming that the months are columns and the rows are employees and named
range ThisMonth contains the current cumulative month number, and the first
column of data is the employee number, and the first row of data is the
month,
try using MATCH to find the row number for the employee and OFFSET to get
the 12 months you need, something like this:

SUM(OFFSET(DataSheet!$A$1,MATCH(EmpNum,DataSheet!$ A$1:$A$20000,0),ThisMonth,1,12))


If you need a weighted moving average you will need to use SUMPRODUCT to
multiple the months by the appropriate weight instead of SUM.

regards

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Bala" wrote in message
...
Hi,

I need to do MAT (Moving Annual Totals) on some employees performance on a
12 monthly basis. In one sheet I have data for these employees and in
another
sheet I need to do MAT (rotating 12 month sums). Problem is in the first
sheet the data keeps changing (updated monthly) so I need to do a vlookup
to
look up the information for any particular employee. Is there a function
that
does this???

Thanks for your help in advance.

Bala