SOLVED

# COMBINE THESE FORMULAS

Copper Contributor

# COMBINE THESE FORMULAS

=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))),""))

7 Replies
best response confirmed by SoyAllenChiu (Copper Contributor)
Solution

# Re: COMBINE THESE FORMULAS

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", "")

# Re: COMBINE THESE FORMULAS

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

# Re: COMBINE THESE FORMULAS

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?

# Re: COMBINE THESE FORMULAS

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?

# Re: COMBINE THESE FORMULAS

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

# Re: COMBINE THESE FORMULAS

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

# Re: COMBINE THESE FORMULAS

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).

1 best response

Accepted Solutions
best response confirmed by SoyAllenChiu (Copper Contributor)
Solution

# Re: COMBINE THESE FORMULAS

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", "")