Hey guys, I have a formula question.
This is the column setup for
Sheet Name=Prices
Product ID = List of Items by name
Description = Irrelevant
Effective Date = At what point the data comes into place.
Purchasing UoM = Irrelevant to the point
Partner/ Vender = Vendor that Supplies listed Product Product ID, and for what Price
Purchase Price = Price by way of Vendor, for said Product ID.
On another sheet I have:
Sheet Name=PRODUCTS
ID=same as Product type
Name = Description
ON INVENTORY SHEETS = Irrelevant
PARTNER / VENDOR = DESTINATION OF NEEDED FORMULA
Goal is to check on the Prices Page, checking according to "ID", and checking who has the lowest price, of there most current Effective Date.
So it would check all Partners that have the specific ID being looked for, then check the most current Effective date, then check who has the lowest price that meet those criteria, and return that value, or Vendor name.
Key Note for understanding, the Prices sheet is a running vertical log, recording each time an ID has a price change, by each vendor, and at what point (date), its effective.
This is the column setup for
Sheet Name=Prices
| PRODUCT ID | DESCRIPTION | EFFECTIVE FROM DATE | Purchasing UoM | PARTNER / VENDOR | PURCHASE PRICE |
Product ID = List of Items by name
Description = Irrelevant
Effective Date = At what point the data comes into place.
Purchasing UoM = Irrelevant to the point
Partner/ Vender = Vendor that Supplies listed Product Product ID, and for what Price
Purchase Price = Price by way of Vendor, for said Product ID.
On another sheet I have:
Sheet Name=PRODUCTS
| ID | NAME | ON INVENTORY SHEETS | PARTNER / VENDOR |
ID=same as Product type
Name = Description
ON INVENTORY SHEETS = Irrelevant
PARTNER / VENDOR = DESTINATION OF NEEDED FORMULA
Goal is to check on the Prices Page, checking according to "ID", and checking who has the lowest price, of there most current Effective Date.
So it would check all Partners that have the specific ID being looked for, then check the most current Effective date, then check who has the lowest price that meet those criteria, and return that value, or Vendor name.
Key Note for understanding, the Prices sheet is a running vertical log, recording each time an ID has a price change, by each vendor, and at what point (date), its effective.
