Forum Discussion
Solved
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))),""))
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", "")
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", "")
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?
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?
- Thank you Hans. after so many days I finally solved this, thanks to you! ❤️ Thank you so much!
