I am looking to pull some data out of our system into a spreadsheet to calculate any shortfalls in (stock at hand)-(weekly sales)+(incoming stock that week).
I have a master results sheet. Another sheet which has stock at hand and weekly sales on and then a third sheet which shows amount of stock coming in and and on which date.
I can vlookup the item ( a product code ) to show stock at hand and then minus weekly sales and show that result for each w/c date through the calendar. The bit I am struggling on is looking up the incoming stocks and adding that to the calculation.
So essentially on the master sheet I need to vlookup on another sheet the product code. Then pull any incoming amounts of stock for that code if it falls between the available w/c date which is at the top of my master spreadsheet.
So at the moment I can find the first instance of that code on the sheet with;
=VLOOKUP($A$14,PD!$A$1:$K$247,8,FALSE) and it brings through the first incoming amount. I need then to check if its between the date range for that columns w/c ie 01/01/22+6 days. Anything after that date the check would be in the next w/c column on my master sheet. The other thing I need to do is sum the amounts if there were more than one date, so if 500 was arriving on 01/01/22 and then another 500 on the 05/01/22 I would need the answer to be 1000 for that w/c date of 01/01/22+6days.
I can then mix that with the other parts of the lookups so the ultimate answer would be
Localstock-avgsales+incomingstock. I am looking for this to basically show me any shortfalls at any point. At the moment the system will say we have for example 14 weeks worth of stock on order, but doesnt tell me without a bit of work if its all going to arrive in time to avoid any out of stock days. It could be 14 weeks worth of stock arriving all on one date 4 weeks after we would have been out of stock.
Any help greatly appreciated. I have done many vlookups but never between certain conditions and not ever having to sum multiple data.
Many thanks and apologies if the above is not clear enough to get a response any questions I will answer of course.
I have a master results sheet. Another sheet which has stock at hand and weekly sales on and then a third sheet which shows amount of stock coming in and and on which date.
I can vlookup the item ( a product code ) to show stock at hand and then minus weekly sales and show that result for each w/c date through the calendar. The bit I am struggling on is looking up the incoming stocks and adding that to the calculation.
So essentially on the master sheet I need to vlookup on another sheet the product code. Then pull any incoming amounts of stock for that code if it falls between the available w/c date which is at the top of my master spreadsheet.
So at the moment I can find the first instance of that code on the sheet with;
=VLOOKUP($A$14,PD!$A$1:$K$247,8,FALSE) and it brings through the first incoming amount. I need then to check if its between the date range for that columns w/c ie 01/01/22+6 days. Anything after that date the check would be in the next w/c column on my master sheet. The other thing I need to do is sum the amounts if there were more than one date, so if 500 was arriving on 01/01/22 and then another 500 on the 05/01/22 I would need the answer to be 1000 for that w/c date of 01/01/22+6days.
I can then mix that with the other parts of the lookups so the ultimate answer would be
Localstock-avgsales+incomingstock. I am looking for this to basically show me any shortfalls at any point. At the moment the system will say we have for example 14 weeks worth of stock on order, but doesnt tell me without a bit of work if its all going to arrive in time to avoid any out of stock days. It could be 14 weeks worth of stock arriving all on one date 4 weeks after we would have been out of stock.
Any help greatly appreciated. I have done many vlookups but never between certain conditions and not ever having to sum multiple data.
Many thanks and apologies if the above is not clear enough to get a response any questions I will answer of course.
