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

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

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

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
>
>