Inventory Status: Reordered (How do I do it?)
Hello,
I am developing an inventory reorder system and I need the status of the line item to show "Reordered" when the check box is checked under "Order Status"
I recognize that there are multiple true statements within my formula so how can I show "Reordered" while the stock quantity is below the ROP? I have been trying to troubleshoot this for over an hour by scouring forms on this site and I still haven't determined a solution
Thanks for any help
Noah
Best Answer

Try this:
=IF([Order Status]@row = 1, "Reordered", IF([Stock Quantity]@row < [Reorder Point (ROP)]@row, "Reorder Needed", IF([Stock Quantity]@row >= [Reorder Point (ROP)]@row, "OK")))
This is what it says:
If the Order Status is Checked (no matter what the Reorder Point says), then say "Reordered"
=IF([Order Status]@row = 1, "Reordered",
Otherwise, if the Order Status is not checked, and if the Stock Quantity is less than the Reorder point, return "Reorder Needed"
IF([Stock Quantity]@row < [Reorder Point (ROP)]@row, "Reorder Needed",
Or otherwise, If the if the Order Status is not checked, and if the Stock Quantity is greater than or equal to than the Reorder point, return "OK"
IF([Stock Quantity]@row >= [Reorder Point (ROP)]@row, "OK")))
Did that work for you?
Cheers!
Genevieve
Answers

It would be helpful to know a bit more about this formula and exactly when you want each of these outputs to be returned.... when should it be Blank? When should it say "Reorder Needed"? When should it say "OK"? When should it say "Reordered"?
Once we know the criteria for each of these outputs, we can build the IF statement to return what you want, based on this criteria.
Logic formulas read left  to  right, and stop as soon as they reach a criteria that works. So, if the first thing you want it to look for is if the checkbox is checked, write that as the very first instruction... ex:
=IF([Order Status]@row = 1, "REORDERED"
Then the rest of the instructions will only happen if the Order Status in that row is not checked.
I also see that your first statement is counting one cell. Is that to see if it's blank or not?
COUNT([Item Name]@row) will either return a 1 if there's content in that cell or a 0 if that cell is blank. Is that the intended instruction?
Let me know if this makes sense or if you still need help!
Cheers,
Genevieve

Hi @Genevieve P thanks for the response.
"OK" is when the Stock Quantity is greater than Reorder Point
"Reorder Needed" is when Stock Quantity is equal to or less than the Reorder Point
"Reordered" is when the check box is checked, even if the stock quantity is less than the reorder point
I am new to Smartsheets (I was introduced to the software last week) so this is all very helpful.
The first part of the function that returns "" is probably not necessary.
Thank you,
Noah

Am I getting closer? This is still giving me an error

Try this:
=IF([Order Status]@row = 1, "Reordered", IF([Stock Quantity]@row < [Reorder Point (ROP)]@row, "Reorder Needed", IF([Stock Quantity]@row >= [Reorder Point (ROP)]@row, "OK")))
This is what it says:
If the Order Status is Checked (no matter what the Reorder Point says), then say "Reordered"
=IF([Order Status]@row = 1, "Reordered",
Otherwise, if the Order Status is not checked, and if the Stock Quantity is less than the Reorder point, return "Reorder Needed"
IF([Stock Quantity]@row < [Reorder Point (ROP)]@row, "Reorder Needed",
Or otherwise, If the if the Order Status is not checked, and if the Stock Quantity is greater than or equal to than the Reorder point, return "OK"
IF([Stock Quantity]@row >= [Reorder Point (ROP)]@row, "OK")))
Did that work for you?
Cheers!
Genevieve

This worked, thank you so much!

I'm so glad this worked for you! 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!