Hi Guys
I have been battling with this issue for a while now after inheriting this spread sheet from a previous employee who has since left the company before I started
I will try to explain my problem in plain English as best as I can, here goes.
I have a spread sheet with two workbooks, "IRs" and "price Codes"
on the IRs workbook I need to calculate the price of scrap produced based on the part number being scrapped by month.
the Work book "Price codes" holds the average price of parts by month in this format
C D E F G H I
Months Month Number CU AG Ni Tin Alloy
3 01/01/15 1 1.00 2.00 3.00 4.00 1.00
4 01/02/15 2 1.00 2.00 3.00 4.00 5.00
5 01/03/15 3 0.00 0.00 0.00 0.00 0.00
6 01/04/15 4 0.00 0.00 0.00 0.00 0.00
7 01/05/15 5 0.00 0.00 0.00 0.00 0.00
8 01/06/15 6 0.00 0.00 0.00 0.00 0.00
9 01/07/15 7 0.00 0.00 0.00 0.00 0.00
10 01/08/15 8 0.00 0.00 0.00 0.00 0.00
11 01/09/15 9 0.00 0.00 0.00 0.00 0.00
12 01/10/15 10 0.00 0.00 0.00 0.00 0.00
13 01/11/15 11 0.00 0.00 0.00 0.00 0.00
14 01/12/15 12 0.00 0.00 0.00 0.00 0.00
The work book "Irs" hold various details about the item being scrapped but my problem is this
I Have the Month Number In Column B of the "IRs" Work Book
I have a part number in column G of the "IRs" work book
I have the cost value in Column Q of the "IRs" Work Book
In column Q I have this code
=(IF(OR(LEFT($G8,2)="BB",LEFT($G8,2)="EB",LEFT($G8,2)="CA",LEFT($G8,2)="EC"),SUMPRODUCT(--('Price Codes'!$D$3:$D$14=$B8)*SUM('Price Codes'!$E$4:$E$4)*$J8),"0.00")-$H$8)
This code looks up the month in the Work book "Price codes" ('Price Codes'!$D$3:$D$14=$B8) based on the month of the entry on the "IRs" work book from cell $B8, then gets the correct price from the "price Codes" work book *SUM('Price Codes'!$E$4:$E$4)*$J8, $J8 holds the quantity, if the part does not start with either BB, EB, CA or EC then 0.00 is returned
This code is getting the correct price if I set the cell reference to the correct cell from the "Price codes" work book $E$4:$E$4 but my question is this
what do I need to change to get the correct price from the "Price Codes" work book automatically based on the month
I have been trying to get my head around this problem for a while now, I am used to working with Access VBA but I am totally new to excel and I am struggling with the syntax.
Any help or pointers would be really really appreciated
I am really sorry if this has been asked before
Steve
I have been battling with this issue for a while now after inheriting this spread sheet from a previous employee who has since left the company before I started
I will try to explain my problem in plain English as best as I can, here goes.
I have a spread sheet with two workbooks, "IRs" and "price Codes"
on the IRs workbook I need to calculate the price of scrap produced based on the part number being scrapped by month.
the Work book "Price codes" holds the average price of parts by month in this format
C D E F G H I
Months Month Number CU AG Ni Tin Alloy
3 01/01/15 1 1.00 2.00 3.00 4.00 1.00
4 01/02/15 2 1.00 2.00 3.00 4.00 5.00
5 01/03/15 3 0.00 0.00 0.00 0.00 0.00
6 01/04/15 4 0.00 0.00 0.00 0.00 0.00
7 01/05/15 5 0.00 0.00 0.00 0.00 0.00
8 01/06/15 6 0.00 0.00 0.00 0.00 0.00
9 01/07/15 7 0.00 0.00 0.00 0.00 0.00
10 01/08/15 8 0.00 0.00 0.00 0.00 0.00
11 01/09/15 9 0.00 0.00 0.00 0.00 0.00
12 01/10/15 10 0.00 0.00 0.00 0.00 0.00
13 01/11/15 11 0.00 0.00 0.00 0.00 0.00
14 01/12/15 12 0.00 0.00 0.00 0.00 0.00
The work book "Irs" hold various details about the item being scrapped but my problem is this
I Have the Month Number In Column B of the "IRs" Work Book
I have a part number in column G of the "IRs" work book
I have the cost value in Column Q of the "IRs" Work Book
In column Q I have this code
=(IF(OR(LEFT($G8,2)="BB",LEFT($G8,2)="EB",LEFT($G8,2)="CA",LEFT($G8,2)="EC"),SUMPRODUCT(--('Price Codes'!$D$3:$D$14=$B8)*SUM('Price Codes'!$E$4:$E$4)*$J8),"0.00")-$H$8)
This code looks up the month in the Work book "Price codes" ('Price Codes'!$D$3:$D$14=$B8) based on the month of the entry on the "IRs" work book from cell $B8, then gets the correct price from the "price Codes" work book *SUM('Price Codes'!$E$4:$E$4)*$J8, $J8 holds the quantity, if the part does not start with either BB, EB, CA or EC then 0.00 is returned
This code is getting the correct price if I set the cell reference to the correct cell from the "Price codes" work book $E$4:$E$4 but my question is this
what do I need to change to get the correct price from the "Price Codes" work book automatically based on the month
I have been trying to get my head around this problem for a while now, I am used to working with Access VBA but I am totally new to excel and I am struggling with the syntax.
Any help or pointers would be really really appreciated
I am really sorry if this has been asked before
Steve