Forum Discussion
Formula for material inventory tracking
I have a material consumption sheet with Materials represented in the lines and projects in columns. The projects are listed by date so that the further I go to the right the more material consumption I have. In one seperate column I have the start inventory for each material.
But in between I have deliveries of new materials which I log in seperate columns for each material: Qty1, delivery date1, qty2, del date2, qty3, del date3.
I need a formula which goes through the lines cell by cell and does the following
1. Check each cell whether the sum of consumption up to that project date x is < inventory qty. If yes, make that cell green, if not, red.
2. Check for each cell whether a new delivery (1/2/3 see above) has arrived (comparing the project date and delivery date) and include this into the formula in 1. so that the green/red classification includes new deliveries.
Frankly, that sounds like a spreadsheet that needs to be redesigned so as to take advantage of Excel more fully, letting IT (Excel) do more of the work for you. Would you be willing to share the file--so long as it contains no confidential information--so some of us can take a look at it and make suggestions?
Post it on OneDrive or GoogleDrive with a link pasted here that grants access to it.
