P
PaulinaDi
Hi:
I have a document that I need to constantly update as I receive new data for
it (as you may see in column named "Rev").
Rev Item Bulk Descr Price
1 1001 1 Tube 2.00
1 1001 1 Tube 2.00
1 1001 2 Tube 3.00
1 1002 6 Valve 5.00
2 1001 1 Tube 4.00
2 1002 6 Valve 8.00
2 1002 8 Valve 6.00
3 1001 1 Tube 4.00
3 1002 8 Valve 6.00
3 1001 2 Tube 4.00
3 1001 2 Tube 4.00
3 1002 8 Valve 10.00
4 1001 2 Tube 6.00
4 1001 1 Tube 5.00
4 1001 1 Tube 5.00
4 1001 2 Tube 6.00
4 1001 2 Tube 6.00
4 1002 6 Valve 10.00
Is there any way to get the price for another sheet using the SUMPRODUCT and
at the same time or just in another step compare if, this spreadsheet
contains this itemcode and bulk size just consider the last version on qty
and price and eliminate all the info before it? and if not, just leave the
info from the latest Revision version?
The formula I'm using to get the Price from the other sheet is:
=SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12),AccCad!$AJ$15:$AJ$1415)/SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12))
BY THE WAY... how may I be able to reduce the formula above as I have to get
the info and then divide it from the values I get in order to get just the
price for one piece? (E contains Item Code, F contains de Bulk Size and AJ
contains the Price)
I have a document that I need to constantly update as I receive new data for
it (as you may see in column named "Rev").
Rev Item Bulk Descr Price
1 1001 1 Tube 2.00
1 1001 1 Tube 2.00
1 1001 2 Tube 3.00
1 1002 6 Valve 5.00
2 1001 1 Tube 4.00
2 1002 6 Valve 8.00
2 1002 8 Valve 6.00
3 1001 1 Tube 4.00
3 1002 8 Valve 6.00
3 1001 2 Tube 4.00
3 1001 2 Tube 4.00
3 1002 8 Valve 10.00
4 1001 2 Tube 6.00
4 1001 1 Tube 5.00
4 1001 1 Tube 5.00
4 1001 2 Tube 6.00
4 1001 2 Tube 6.00
4 1002 6 Valve 10.00
Is there any way to get the price for another sheet using the SUMPRODUCT and
at the same time or just in another step compare if, this spreadsheet
contains this itemcode and bulk size just consider the last version on qty
and price and eliminate all the info before it? and if not, just leave the
info from the latest Revision version?
The formula I'm using to get the Price from the other sheet is:
=SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12),AccCad!$AJ$15:$AJ$1415)/SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12))
BY THE WAY... how may I be able to reduce the formula above as I have to get
the info and then divide it from the values I get in order to get just the
price for one piece? (E contains Item Code, F contains de Bulk Size and AJ
contains the Price)