Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 sheets and code
hey all,
let's say i have a list of employee id's on sheet1. And on sheet 2 there's a list of employee records vacation hours taken (1-to-many). what is the best way to iterate thru sheet1 and sum up all the hours for each employee in sheet2 and place the total in a new column in sheet1 next to respective employee? sheet1 id,name,total hours 1,john doe,16 sheet2 id,hours,date 1,4,5/6/07 1,4,12/1/06 1,8,3/12/06 thanks, rodchar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 sheets and code
SumIf. Example below is assuming that the employee ID is in column A
of both sheets and that the hours taken is in column B of sheet2. It is also assuming that we are dealing with the record in A2 of sheet1. =SUMIF(Sheet2!A:A,Sheet1!A2,Sheet2!B:B) rodchar wrote: hey all, let's say i have a list of employee id's on sheet1. And on sheet 2 there's a list of employee records vacation hours taken (1-to-many). what is the best way to iterate thru sheet1 and sum up all the hours for each employee in sheet2 and place the total in a new column in sheet1 next to respective employee? sheet1 id,name,total hours 1,john doe,16 sheet2 id,hours,date 1,4,5/6/07 1,4,12/1/06 1,8,3/12/06 thanks, rodchar |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 sheets and code
Say Sheet2 goes on for 100 rows. Then in C1 of Sheet1 enter:
=SUMPRODUCT(--(Sheet2!A$1:A$100=A1),(Sheet2!B$1:B$100)) and copy down -- Gary''s Student - gsnu200743 "rodchar" wrote: hey all, let's say i have a list of employee id's on sheet1. And on sheet 2 there's a list of employee records vacation hours taken (1-to-many). what is the best way to iterate thru sheet1 and sum up all the hours for each employee in sheet2 and place the total in a new column in sheet1 next to respective employee? sheet1 id,name,total hours 1,john doe,16 sheet2 id,hours,date 1,4,5/6/07 1,4,12/1/06 1,8,3/12/06 thanks, rodchar |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 sheets and code
Thanks all for the great insight,
rod. "JW" wrote: SumIf. Example below is assuming that the employee ID is in column A of both sheets and that the hours taken is in column B of sheet2. It is also assuming that we are dealing with the record in A2 of sheet1. =SUMIF(Sheet2!A:A,Sheet1!A2,Sheet2!B:B) rodchar wrote: hey all, let's say i have a list of employee id's on sheet1. And on sheet 2 there's a list of employee records vacation hours taken (1-to-many). what is the best way to iterate thru sheet1 and sum up all the hours for each employee in sheet2 and place the total in a new column in sheet1 next to respective employee? sheet1 id,name,total hours 1,john doe,16 sheet2 id,hours,date 1,4,5/6/07 1,4,12/1/06 1,8,3/12/06 thanks, rodchar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
Same code in several sheets.. | Excel Programming |