Doing a sum in way of Vlookup???

[Guest]

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

 

[Zack Barresse]

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

 

[Charles Williams]

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

 

[Guest]

Thanks Zack & Charles. Appreciated.

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