Simple Inventory Tracking Sheet

I wasn't quite clear about which page you wanted the countdown formula on. I think it was "Inventory," so I used the following formula in C2, filled down and right:

=[@[6/13/2018]]-INDEX(Table1[[Item 1]:[Item 5]],MATCH(VLOOKUP(DATE(YEAR(Table2[[#Headers],[6/13/2018]]),MONTH(Table2[[#Headers],[6/13/2018]]),DAY(Table2[[#Headers],[6/13/2018]])),Table3[[Date]:[Recipe]],2,FALSE),Table1[[Recipe]:[Recipe]],0),MATCH($A2,Table1[[#Headers],[Item 1]:[Item 5]],0))

I did the same for "Schedule," using the following in C3, filled right and down:

=C2-VLOOKUP(Schedule!$B2,Table1,MATCH(Table3[[#Headers],[Item 1]],Table1[#Headers],0),FALSE)

I don't recommend using both of my formulas. I would advise using one, then having the sheet you don't choose take its data from the one you do. Take a look at the attachment to see if it's working as desired:

There's an HLOOKUP that functions has a horizontal version of VLOOKUP, searching for the specified value in the first row of the range and returning the result from the specified return row. Alternatively, you can use INDEX/MATCH, roughly like this: INDEX(return range, MATCH(value to look for, range to look in, zero)) in place of either LOOKUP. I prefer the INDEX/MATCH combo, but use what you're comfortable with. As to modifying your formula for 17 items, change the "Item 5"s in the formula to "Item 17" and I think it should work. In your formula in post 3, there's an early "item 5" you missed:

=[@[6/13/2018]]-INDEX(Table1[[Item 1]:[Item 5]],MATCH(VLOOKUP(DATE(YEAR(Table2[[#Headers],[6/13/2018]]),MONTH(Table2[[#Headers],[6/13/2018]]),DAY(Table2[[#Headers],[6/13/2018]])),Table3[[Date]:[Recipe]],2,FALSE),Table1[[Recipe]:[Recipe]],0),MATCH($A2,Table1[[#Headers],[Item 1]:[Item 17]],0))

If you're still getting errors, post an attachment with all 17 items incorporated for a better diagnosis.

Maybe Im missing something, but this seems to work, as well...
=iferror(INDEX(Schedule!$C$2:$G$17,MATCH(--Inventory!B$1,Schedule!$A$2:$A$17,0),MATCH(Inventory!$A2,Schedule!$C$1:$G$1,0)),"")

Not really sure where/how the Recipe comes in?

EDIT: The forum's firewall was blocking my post for reasons I can't quite determine, so I added some spaces into the formulas below to bypass the firewall. You can ignore/eliminate those or just pull the proper formulas from the attachment.


Hopefully I've got it all:

1) The issue with 6/26 is that there was no recipe chosen on your schedule tab, so the formula couldn't calculate the new totals for that date, nor for any date thereafter. Add a recipe for the day and the problem goes away.

2) The issue you're having dragging the column for more dates arose because sometimes using tables is more trouble than it's worth. Table headers can't be formulas, so they don't always respond as you might expect to things like dragging. To solve the problem without destroying your table, I went to the "design" part of the ribbon for your table on the inventory tab. Under "design", I unchecked the "Header Row" box, which keeps your table but eliminates the headers in row1. These were easily replaced with a hard date in C1 and =C1+1 in D1, filled right for as far as you like. Your table now as unofficial headers, which can better receive formulas.

3) The INDEX formula on 'Schedule' needs to use MATCH to find the correct row and column, as I poorly described in post #4 and FDibbins more ably demonstrated in post #5. The following should work in D2, filled right and down:

=INDEX(Table2[# All],MATCH(D$1,Table2[ [# All] ,[Material ' #]],0),MATCH($A2,Inventory!$1:$1,0))

4) There are potential formatting issues on your 'inbound' tab - the material you enter on 'inbound' must be a perfect format match for the materials listed on 'inventory', which can be a headache since some of the materials look like numbers. To (hopefully) simplify the issue, I changed the five materials columns on 'inbound' to dropdowns that feed off of the list from 'inventory', which should guarantee a format match. I then went with the following formula in D2 of the inventory tab, filled down and right. The new SUMPRODUCT element should account for the 'inbound' entries.

=C2-INDEX(Table1[[402449]:[Column2]],MATCH(VLOOKUP(C$1,Table3[[Date]:[Recipe]],2,FALSE),Table1[[Recipe]:[Recipe]],0),MATCH($A2,Table1[[# Headers],[402449]:[Column2]],0))+SUMPRODUCT(Table5[[QTY]:[QTY5]],(Table5[[Material]:[Material5]]=$A2)*(Table5[Date] < =D$1))

With any luck, that covers at least most of it. Take a look at the attachment to see if it's working as desired:

Works great! Thanks so much for your help!

I've marked this solved but if you're still willing to give me some pointers...

I'd like to add an "Days On Hand" feature that will calculate the number of days of stock remaining for each item of the recipe (inventory/daily usage of the item) and return the lowest number as the maximum days that a recipe can be ran before running out of one or more of the recipe's items. My plan was to add a column in the "Schedule" tab for each material to calculate this then import the lowest figure of the row into a column labeled "Days on Hand". The calculation columns would then be hidden to clean it up a bit.

I've added a couple of these columns in the attached example in the highlighted columns.

Is this the best way to accomplish this or is there an easier way?

Also, I'd like to create a formatting rule to format the text of a cell green on the "Inventory" tab when inventory is added from the "Inbound" tab. Any advice on this?

Thanks again for all your help already!

Glad to help!

First, I spotted an error. The formula in D2 of inventory should be:

=C2-INDEX(Table1[[402449]:[Column2]],MATCH(VLOOKUP(C$1,Table3[[Date]:[Recipe]],2,FALSE),Table1[[Recipe]:[Recipe]],0),MATCH($A2,Table1[[#Headers],[402449]:[Column2]],0))+SUMPRODUCT(Table5[[QTY1]:[QTY5]],(Table5[[Material1]:[Material5]]=$A2)*(Table5[[Date]:[Date]]=D$1))

The previous version was calculating the inbound repeatedly.

For your "Days Left" count, your way should work, but I would advise working from the 'Recipes' tab so you can easily get the info for each recipe. I created a new column to show the first date that each recipe would fail to have the necessary ingredients. This date was calculated with the following formula, array entered (confirmed with Ctrl + Shift + Enter) in B2:

=SMALL(IF(IFERROR((Table3[Recipe]=[@Recipe])*(Table3[[Recipe]:[Column2]]<0),0),Table3[Date]),1)

Next to that, I created a column with the remaining number of days that each recipe is scheduled before the fail date, using the following (non-array) in C2:

=COUNTIFS(Table3[Recipe],[@Recipe],Table3[Date],"<"&[@[Fail Day]])

For the conditional formatting, just select C2:HT19 on inventory and use the following CF formula:

=C2>B2

Experiment with the attachment a bit to see if everything is working as desired: