Hi people, I am a totally new on this, my basic knowledges do not help me much on what I am trying to achieve. Hopefully I can find an answer here.
Description on what I am trying to archive is on sheet2.
Thank you
Hi people, I am a totally new on this, my basic knowledges do not help me much on what I am trying to achieve. Hopefully I can find an answer here.
Description on what I am trying to archive is on sheet2.
Thank you
I'm having a difficult time understanding the description in your file, but based on your expected results, I put the following formula in D2, then filled down:
=IF(IFERROR(VLOOKUP(A2,Sheet3!$A$2:$D$25,4,FALSE)-SUMIF($A$1:$A1,$A2,$D$1:$D1),0)>0,MIN(C2,VLOOKUP(A2,Sheet3!$A$2:$D$25,4,FALSE)-SUMIF($A$1:$A1,$A2,$D$1:$D1)),"")
And in E2 and down:
=IF(D2="",C2,C2-D2)
Take a look at the attachment to see if I'm on the right track. If I'm not, please try to provide a clear explanation of what is missing and a few more expected results.
If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".
If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
https://www.excelforum.com/the-water...ke-thread.html
Hi CAntosh,
Sorry that i did not explain well. I tried to do something different and did attach my file. Hopefully we can get an answer.
Thanks for your help.
I believe the formulas I provided in post #2 provide the results you're looking for. I've applied them to the attachment in post #3 and they seem to fit what you're describing. Can you please look at the attachment and explain exactly what it is doing incorrectly?
For some reason I messed up with the formula, it dit work well, thank you so much.
One last question, is there any idea as to put an alert, highlight on red or do something in Column D from sheet2 when a requested quantity from sheet2 cannot be covered by sheet2 column D?
Attach in red what I am taking about.
Thanks again.
If I'm understanding correctly, then select A2:E23, select conditional formatting --> new rule --> use a formula, then use the following formula:
=VLOOKUP($A2,Sheet3!$A$2:$D$7,4,FALSE)>SUMIF($A$1:$A1,$A2,$D$1:$D1)+SUMIF($A2:$A$23,$A2,$C2:$C$23)
I'm not sure whether you just want to highlight just the zero-quantity rows that qualify or all rows with the item# that qualifies. The formula above will do the latter. Hopefully that's what you're looking for? Experiment with the attachment to see if it works as desired:
EDIT: You can delete column F in the attachment, it's just a test column indicating which rows (those marked TRUE) will trigger the formatting.
Last edited by CAntosh; 09-20-2017 at 11:25 AM.
Thank you so much CAntosh, works handsomely!
Excellent, glad to help! Good luck!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks