Daily Reporting where item data changes on regular basis

Hi,

I create a daily sale report in excel for merchants of the product to bill them as per sale/lead I generate on daily basis. My merchants send me price changes of the product very frequently almost twice-thrice in a week with daily amount limit I can bill them maximum per product. Based on those changes I have to create a report for them to bill monthly.

I paste their changes request in different sheet date wise as given in attach file from where I take reference for product rate and daily spent limit. If i don't receive a change request on a particular date then I have to consider previous earliest date changes and use in my reporting. I am looking for a formula which can pull Rate and Limit/day from my change request sheet automatically by date and product id by delivery type.

Any help will be appreciable.

Hi,

Please see the file attached.

I changed the format a bit..

In my workbook the formula Rates and Limits on date 11 Apr, 2015 are zero as there is no data for that date on Change Request sheet. If you add the data for that date, the formula will work OK.

Use this new file.. I have altered the formula to handle more change requests..

Didn't my solution get you the answer?

Originally Posted by sumit.tailshift

Your answer is very close but modification you made in Change Request is not feasible for me. Sorry for that

What is wrong with the solution? Let me know, I will help you out..

1. Format of Change Request, I have a list of 1000 products and for each product I may not receive price/budget change so I have to keep previous change request, if I paste in single column then it will be time consuming.
2. As I said I may not receive daily change request in that case I have to use last date price/budget.
Thanks

For point number 1, it will be slightly time consuming, but that is the right way of handling data sets. for Point number 2, If you do not receive a change request, dont mention that product again, it will automatically get the price of the latest entered date..