Need help to write a formula in cell K9 to add the highest value without causing errors.
We have a current QTY of 30 cases on the floor. We have replenishment orders that will add additional inventory, but have also reserved product for future orders. We can still add an order, but for how much? I figured this is 20, but need a formula to determine easily.
Can someone help me please?
Thanks
Adam
Last edited by AliGW; 09-19-2019 at 12:55 AM. Reason: Irrelevant section of title removed: this is a HELP forum!!!
Maybe this:
=SUMIF($2:$2,"Replenish",$9:$9)+SUMIF($6:$6,"Current",$9:$9)-SUMIF($2:$2,"Reserved",$9:$9)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Mr. Kennedy,
You are a gentleman and a scholar. Thank you!
Best,
Adam
And you're not a bad chap, either!!
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Thank you, Sir!
So.. it seems this didn't quite work as we'd hoped... once I removed a future reserve, an error popped up.
Still trying to work this one out! If you have any additional thoughts, they'd be more than welcomed!
Best,
Adam
Sh1t happens. I'll take a look.
Maybe this in L9:
=E9-SUMPRODUCT((ISNUMBER(F6:K6))*F9:K9)
Unfortunately cant change any of the formulas in the row..
L9 calculates the inventory at a future date. So if we have a new order, we'd only be able book 20 (in K9) without triggering errors (causing future inventory to be <0)
Going out for dinner now. How can I get it to work, without changing formulae.... either current or new??
Is there some sort of formula that works similarly to a what-if? Meaning, something that can hit a MAX number without causing "ERROR" in a row?
Just saw your note. I just asked a coworker to take a look as well.. It's not an easy one.
Bc of the way this all works, the only cell that can be altered (besides the hard-coded) is K9... total pain!
I think my report is going to have to be more complicated than originally anticipated.. Such is life. Can't thank you enough, Glenn.
Finally figured it out..
See attached FYI.
Simple as a MIN formula.. Just needed to rack my brain a bit.
Thank you VERY MUCH!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks