Vlookup advanced - might be a different formula needed (Return cell value)

Attached is a file I am working on and cannot for the life of me get the result I desire.
Basically I want to bring back the date (from the 'Forecast' tab) by which the stock we have gets consumed by the forecast. The stock we have is physical_qty in column D of the 'MASTER' tab.
If you take a look at the spreadsheet attached it has two tabs. The first 'MASTER' is where I want the calculation/result to be shown. The second is the data dump 'Forecast' which I will manually update each week/on the fly - This sheet cannot be changed as it is a report that is dropped out of a piece of software I use.

I have manually put in column I of the 'MASTER' tab a couple of results I want it to get out of this, but cannot work out a forumula to do it automatically for me.

If anyone can make it possible I would be very greatful. I would also like to know how it works as well.
MRP Runout TOP 50.xlsx

I'm not 100% certain what you want here.

I assumed you run this report daily. Otherwise you will likely need to replace the today() with a set day when your report was last run.

I believe this will get you close to what you're looking for. However there is a discrepancy on the days cover and the dates that you are expecting. Not sure if this is due to the frequency the report is ran or not counting weekends.

Formula:

Please Login or Register  to view this content.

Hope this helps.

I took a second look, and it does appear that this report you pulled likely is 4 days old thus why it is off by 4 days on your outcome. You will need to replace the today() with a cell that stores the date when the report was pulled.

And I fixed up your equation a bit more to work more like you want.

Formula:

Please Login or Register  to view this content.

Hi cheal2,

Thanks for the reply.
The only problem I have is that my formula for 'Days Cover' is not as accurate as I would like. Basically below are three examples which I can explain what I am trying to get at:
Take CH048, we have 12080.7 physical stock, if you then go into the 'Forecast' tab and add up when that will run out, it should read Mo 20/05/2013.
The other example would be VA004 which has 54828.5 physical stock and using the same method as above would run out Mo 27/05/2013.
The final example, CO004 has 78709.2 in physical stock which should provide the result Su 14/04/2013

Can you find/think of better way to do this?

Thanks,
Tom.

This is the error I mentioned previously.

This solution assumes that this report is pulled daily. Because the data appears to be four days old, all days are 4 over.

Therefore to solve this, you need to replace the today() function in column I with a reference to a cell that contains the date the report was run.

In other words, in J1 put in 4/8/2013

Then put the following equation in i2 and drag to the bottom.

Formula:

Please Login or Register  to view this content.

This should rectify the problem. You just need to update J1 with the correct date as you proceed to pull more reports. You can also format column I to display the date to your desired format.

Thanks cheal2,

If you take VA004 as the example to focus on here; if you amend the formulas as you have suggested it says that it should have 41.37 and run out 19/05/2013 but from doing it manually I can see that it will run out 27/05/2013.

Is there a way to make it do this?

Hope I am not being a pain, but I feel we are getting somewhere and it would be a massive help if you are able to solve it for me!

Originally Posted by cheal2

How are you getting the values in D?

The physical_qty column D is refreshed from a SQL query. These are refreshed as and when needed. I believe that the formula in column H is the one that needs looking at as it only brings back a rough figure.

The forecast report for some unknown reason drops out data for the 91 days but starts at TODAY()-1. If we take that into consideration then a new total needs to be in place which shows the forecast quantity from todays date till the end of the report.
My thoughts were that to work out Days Cover you could do a lookup on the code (sticking with CO004 as the example here), it look the physical qty (78709) and then went into the forecast tab and worked out when that would be consumed starting from todays date.
See attachment.screenshot.jpg

Thank you mate! That looks amazing...from what I can see it does the job, thanks again - will test further on Monday when back in the office.
My next idea is to make it so that it is a full MRP spreadsheet which then orders each SKU by the closest date to stock out so that my team can then deal with those SKU's and put a purchase order on. Then refresh the data from the query and it will make that SKU disappear so they then move onto the next.

I don't know about auto-ordering, but you could add conditional formatting to column I that lets you know the top products needing replenishment or do it based on needs reordering in a week.