COMBINE THESE FORMULAS

[SoyAllenChiu]

=IF(H43="ALIAS ID 1", IF(O43>='SHEET2'!$G$602,"NSF","")

=IF(H43="ALIAS ID 2", IF(O43>='SHEET2'!$G$707,"NSF","")

=IF(H43="ALIAS ID 3", IF(O43>='SHEET2'!$G$812,"NSF","")

=IF(H43="ALIAS ID 4", IF(O43>='SHEET2'!$G$1132,"NSF","")

=IF(H43="ALIAS ID 5", IF(O43>='SHEET2'!$G$1562,"NSF","")

=IF(H43="ALIAS ID 6", IF(O43>='SHEET2'!$G$1782,"NSF","")

The above formulas should be together. That means if in cell H43 it finds 1 of the criteria, it can check for greater than in sheet 2 according to chosen cell.

At the end after approved that in sheet 2, the cell greater than sheet 1 o43, formulas will switch to this one below!

=(IFNA((IFS("MEMBERSHIP PAID"=(S43),(1),"MEMBERSHIP RENEWED"=(S43),(1),"PENDING"=(S43),(0),"MEMBERSHIP SKIPPED"=(S43),(-1),"MEMBERSHIP MISSED"=(S43),(-1),"MEMBERSHIP UNPAID"=(S43),(-1),"MEMBERSHIP CANCELLED"=(S43),(-1),"MEMBERSHIP VOID"=(S43),(-1))),""))

[#2 · Accepted solution]

@SoyAllenChiu 

IF(OR(AND(H42="ALIAS ID 1", O43>='SHEET2'!$G$602), AND(H42="ALIAS ID 2", O43>='SHEET2'!$G$707), AND(H42="ALIAS ID 3", O43>='SHEET2'!$G$812), AND(H42="ALIAS ID 4", O43>='SHEET2'!$G$1132), AND(H42="ALIAS ID 5", O43>='SHEET2'!$G$1562), AND(H42="ALIAS ID 6", O43>='SHEET2'!$G$1782)), "NSF", "")

[#3]

Thank you Hans. after so many days I finally solved this, thanks to you! ❤️ Thank you so much!

[#4]

Hi I have a problem. The solution you gave me is good. But I am still stuck on something. The greater than is to prevent negative amounts. In my case after applying your solution, I noticed that it will keep on using the o43 as reference for checking greater than. What I want the formula to do is only check greater than, and if it does, only apply once the rule. That means if in Sheet 2 g602, we have 100 and in sheet 1 o43 we have 75, here everything is good. But somehow after calculations, Sheet 2 g602 will return to 25, and the formula is keep on using o43 to find greater than, and it does, and it displays nsf. The formula should only do that only if I tried to add for example input into new row such as o45, o47,. and if it discovers that after new data being entered in o45, for example o45 is 30, here is when it should applied the nsf result, not after the first input. I am trying to prevent negative numbers. Thats why i am using greater than, and also using a culumn in Sheet 1 named status to display nsf

How can I fix this bug?

[#5]

@SoyAllenChiu 

That's confusing (to me), but if I understand you correctly, you don't want the result to change if SHEET2!G602 changes. If so, it would either require iterative calculation or VBA.

In which cells in column O will you be entering data?

[#6]

Let me elaborate: Sheet 2 g602, the value will keep on changing based on data being inserted or calculations from other sheets. Now the question is: How can I tell excel to use the solution you gave me but only apply the rule of your solution "greater than" only once? The thing is, if Sheet 2 g602 is 100 and in sheet 1 o43 is 75, here the formula think it's okay. But right after 100 - 75, the new value of sheets 2 g602 will be 25. Now it shouldn't display NSF BECAUSE IT ALREADY CHECKED ON THAT ERROR. But If I insert a new row in Sheet 1, o45, 30, here is when it should display nsf because 30 is greater than 25. But right now with your solution it solved my issue but it keeps on using o43 value 75 to compare it and it is greater than sheet 2 g602 value 25

[#7]

In this link I listed 2 pictures. The one you will se Sheet 2 g602 and the other Sheet 1 where NSF is displayed, and a little bit context on what is happening.

https://1drv.ms/f/s!AnmhMQXb71KTi95N4ZQzWWy-utoLEw?e=he2Ifu

[#8]

@SoyAllenChiu 

Right-click the sheet tab of the sheet with the payment methods.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OldVal, NewVal
    Dim r As Long
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Range("O42:O100000"), Target) Is Nothing Then Exit Sub
    Application.enablevents = False
    NewVal = Target.Value
    Application.Undo
    OldVal = Target.Value
    Target.Value = NewVal
    If OldVal = "" And NewVal <> "" Then
        r = Target.Row
        Range("V" & r).Value = _
            Evaluate("IFERROR(IF(OR(" & _
                "AND(H" & r & "=""ALIAS ID 1"", O" & r & ">='SHEET2'!$G$602), " & _
                "AND(H" & r & "=""ALIAS ID 2"", O" & r & ">='SHEET2'!$G$707), " & _
                "AND(H" & r & "=""ALIAS ID 3"", O" & r & ">='SHEET2'!$G$812), " & _
                "AND(H" & r & "=""ALIAS ID 4"", O" & r & ">='SHEET2'!$G$1132), " & _
                "AND(H" & r & "=""ALIAS ID 5"", O" & r & ">='SHEET2'!$G$1562), " & _
                "AND(H" & r & "=""ALIAS ID 6"", O" & r & ">='SHEET2'!$G$1782)), " & _
                """NSF"", """"), """")")
    End If
    Application.EnableEvents = True
End Sub

Replace the aliases and the name of SHEET2 with the actual values.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).