rkspeaks
Member
Hi All,
I have a problem in building a Sales report. According to the date in "Sales" sheet I should get the cost of the selected product. The cost is in "Itemlist" sheet. Every month the cost may change or may not change which is captured in Price change table in "Itemlist" sheet. But for all the days IN A MONTH, the cost is the same. If I enter the Date of transaction, Product and Customer, automatically I should get the cost of the product. If the cost changes in the next month, that should apply to the sales entered in the "Sales" sheet for all next month's sales. So the "lookup_value" is the month in a date. According to that we can do something to get that done. But this will become two way look up. I tried Index/Match function but having difficulty with the matching of the partial dates i.e. Matching of the month in "Sales" sheet with that of in "Itemlist" sheet.
I tried searching in the Chandoo.org archives, but I did not see anything resembles my problem.
I think I have explained clearly. Any doubts please come back. Below is the Download link.
https://hotfile.com/dl/165988554/7973851/Inventory.xlsx.html
Regards
RK
I have a problem in building a Sales report. According to the date in "Sales" sheet I should get the cost of the selected product. The cost is in "Itemlist" sheet. Every month the cost may change or may not change which is captured in Price change table in "Itemlist" sheet. But for all the days IN A MONTH, the cost is the same. If I enter the Date of transaction, Product and Customer, automatically I should get the cost of the product. If the cost changes in the next month, that should apply to the sales entered in the "Sales" sheet for all next month's sales. So the "lookup_value" is the month in a date. According to that we can do something to get that done. But this will become two way look up. I tried Index/Match function but having difficulty with the matching of the partial dates i.e. Matching of the month in "Sales" sheet with that of in "Itemlist" sheet.
I tried searching in the Chandoo.org archives, but I did not see anything resembles my problem.
I think I have explained clearly. Any doubts please come back. Below is the Download link.
https://hotfile.com/dl/165988554/7973851/Inventory.xlsx.html
Regards
RK
