I am trying to make a sort of "inventory helper" spreadsheet that can track quantities. On the summary sheet, I want to be able to enter a quantity into a column called "On Hand" and then if that number is under an established threshold (which will be different depending on the item the row is for) a column further across will display a yellow and/or red message informing whoever is using the sheet that they need to order more of said-item. Perhaps taking this one step beyond, there would be another column which takes the difference between the required amount and the quantity on hand so that it would display exactly how many more of the item needs to be ordered, too.
I've seen some spreadsheets that do things similar to this, but I have absolutely no idea how to do it myself.
Any help would be greatly appreciated!
Excel – How to create spreadsheet that will alert me when a quantity drops below a certain threshold in Excel 2010
microsoft excelmicrosoft-excel-2010worksheet-function
Related Solutions
You can do this with a simple CountIF() of the items ordered, nested in an IF() statement. If the count is zero, the "False" part of the IF() statement will be executed. If the count is greater than zero, the "True" part of the IF() will be executed, since any value other than 0 is equal to TRUE. So, in your first sheet, enter this into row 2 and copy down.
=IF(COUNTIF('Recent POs'!A:A,A2),"recently ordered","")
For an example see:
Best Answer
Basically this is called Conditional formatting in Excel. Since you are talking on different thresholds for each item, it will be best to have a threshold column as well so you can change what the threshold will be in the future easily. I could go into great detail on how to do this, however, there is a great place where this is already documented at: https://www.ablebits.com/office-addins-blog/2014/06/05/excel-conditional-formatting-icon-sets-data-bars-color-scales/. This tells you not only on how to do the conditional formatting, but also on what different types of formatting is available to you, and applies to not only Excel 2010, but also from Excel 2007 to currently 2013.