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(...
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?
- 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
- 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