Forum Discussion
Excel - Matching outgoing to incoming inventory based a specific conditions
Hello, I wonder if anyone here can help me. I have created an inventory tracking file in excel but I have encountered a difficulty that I cannot seem to find an answer for. I have sequenced eac...
=IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"closed",
IF(SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"open",
IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)>0,"in progress","")))This is the formula in cell G2.
=IF(A2<>"","",INDEX($A$2:A2,LARGE(IF($A$2:A2<>"",ROW($A$1:A1)),1)))This is the formula in cell B2. The formula must be entered as an arrayformula with ctrl+shift+enter if you work with legacy Excel such as Excel 2013.
Both formulas must be filled down.
Hello Oliver,
I copied the formulas into my file - one worked (Status), one did not ("Out" sequence)
I get a #REF! error when I insert the following formula:
=IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$6:D6)),1)))
I can't seem to attach a file so here is a snip, would you mind taking a look?
Again, thank you for your help!
CHristine