I have a table I use for the main order entry sheet,I have a separate table for products which has 7 column headers
Code Mfg Description Cost Price FlatRateShip CostDate, my purchase orders and sales receipt forms
are programmed with vlookup statements that key off the order number from the main order entry sheet to populate themselves.
The problem I am having is when manufacturers raise their prices during the course of the year how do I create a statement that
will populate my sales receipt and purchase order forms with the most current price.
The following is the current statement in my invoice to insert the price =IF(B5=0,0,VLOOKUP(B5,'P3'!$A$2:$G$9995,5,FALSE))
I would like to know how to write a statement that would return the price of the product from an item this is the most current price
as the column headers shown above suggest the last column CostDate is the date when this price became effective, how do I get Excel
to look at the code number and if there are more than one of the same code number select the price of the code number that has a CostDate
that is equal to or later than the invoice date. My invoice date is located in cell G3.
Thank you for any light you may be able to shed on this problem.
Code Mfg Description Cost Price FlatRateShip CostDate, my purchase orders and sales receipt forms
are programmed with vlookup statements that key off the order number from the main order entry sheet to populate themselves.
The problem I am having is when manufacturers raise their prices during the course of the year how do I create a statement that
will populate my sales receipt and purchase order forms with the most current price.
The following is the current statement in my invoice to insert the price =IF(B5=0,0,VLOOKUP(B5,'P3'!$A$2:$G$9995,5,FALSE))
I would like to know how to write a statement that would return the price of the product from an item this is the most current price
as the column headers shown above suggest the last column CostDate is the date when this price became effective, how do I get Excel
to look at the code number and if there are more than one of the same code number select the price of the code number that has a CostDate
that is equal to or later than the invoice date. My invoice date is located in cell G3.
Thank you for any light you may be able to shed on this problem.