Data Comparison
You can use a vlookup to pull the time to make each part from the first sheet
into the second.
Assuming your data is as follows on the first Sheet
A B
1 Part # Time Req
2 ABC123 .5
3 ABC456 .1
On the second sheet
1 A B C D
2 Part # Qty Due Date Time Req for Lot
3 ABC123 100 2/1/11 50
4 ABC123 200 4/1/11 100
5
6
the formula for D3 would be
=Vlookup(A3,Sheet1!$A$2:$B$3,2,False)*B3
Copy down as needed.
Once you have time for each lot you can use a sumproduct to get hours needed
for a time period
for example
=SUMPRODUCT(--(A6:A7="ABC123"),--(MONTH(C6:C7)=2),--(YEAR(C6:C7)=2011),D6:D7)
Would give you the total hours for ABC123 due in Feburary 2011
--
If this helps, please remember to click yes.
"Ktran" wrote:
I have a sheet with a list of part numbers and the time it takes to make each
part. On another sheet, I have a list of part numbers with the quanity due
and and due date. I am trying to find out a way to calculate the total hours
need to make all parts in a given week, month, quarter, etc...
|