Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |