Break IF formula if equals specific value

[henrique218]

Hello everyone,

I have a sheet that is constantly updating (every half second) a value from another software connected to excel.
And I also have one cell with a IF condition if this value (dynamic) is greater than a target, the IF cell = DONE.

The problem is that this value is constantly updating as I said. So in one second can be lower than the target.

I need a help to break IF formula if TRUE statement. Is it possible?

THank you

Exame

Column A (dynamic value)	Column B (Target)	IF formula
33	24	DONE
12	24	=IF(B2<A2;"DONE";"")
11	20	=IF(B3<A3;"DONE";"")
22	20	DONE

 

[DacEasy]

The way I break an if statement is with a GoTo Statement. Hope that helps, see below:

If catWords.Columns(1).Rows(j).Value = "" Then
                GoTo part2
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(1).Rows(j).Value)) > 0 And Range("E" & i).Value = "" Then
                Range("E" & i).Value = " Other"
            End If
            
part2: 'Since the columns lengths on the Category Sheet vary, I used goto to exit the if statement when blanks appears in the columns
            If catWords.Columns(2).Rows(j).Value = "" Then
                GoTo part3
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(2).Rows(j).Value)) > 0 And Range("E" & i).Value = "" Then
                    Range("E" & i).Value = " CPU"
            End If
            
part3:
            If catWords.Columns(3).Rows(j).Value = "" Then
                GoTo part4
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(3).Rows(j).Value)) > 0 And Range("E" & i).Value = "" Then
                Range("E" & i).Value = " Memory"
            End If
            
part4:

 

[henrique218]

The way I break an if statement is with a GoTo Statement. Hope that helps, see below:

If catWords.Columns(1).Rows(j).Value = "" Then
                GoTo part2
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(1).Rows(j).Value)) > 0 And Range("E" & i).Value = "" Then
                Range("E" & i).Value = " Other"
            End If
           
part2: 'Since the columns lengths on the Category Sheet vary, I used goto to exit the if statement when blanks appears in the columns
            If catWords.Columns(2).Rows(j).Value = "" Then
                GoTo part3
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(2).Rows(j).Value)) > 0 And Range("E" & i).Value = "" Then
                    Range("E" & i).Value = " CPU"
            End If
           
part3:
            If catWords.Columns(3).Rows(j).Value = "" Then
                GoTo part4
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(3).Rows(j).Value)) > 0 And Range("E" & i).Value = "" Then
                Range("E" & i).Value = " Memory"
            End If
           
part4:

Hi DacEasy, thank you for your help. Is this vba is constantly running? And as I understand this part of the code is inside a loop like for. Is it right?
I was thinking to use formula instead of vba, because my value is constantly updating every half second

 

[DacEasy]

Oh wow, I just noticed that you are doing an =if statement. I am not sure on those, I mainly use vba. But if it's constantly fast updating, controlling =if would be a pain. Maybe another person will supply a solution.

 

[Joe4]

Maybe its just me, but I am not exactly sure what you mean when you say you want to "break IF formula if TRUE statement".
Can you clarify/explain in more detail.

 

[henrique218]

Maybe its just me, but I am not exactly sure what you mean when you say you want to "break IF formula if TRUE statement".
Can you clarify/explain in more detail.

Joe4, I have one api connected in my excel file. This update a value in a column every half second (i.e. stock price) and I have one IF FORMULA to check if the price is greater than or lower than a target. And if this value is greater than I need to stop IF in true argument and stop. The problme is because the value is dynamic and can change every moment, so I need to stuck in the first time that IF FORMULA get true value. Do you understand?

 

[Joe4]

Are you saying that for any particular row, the first time your formula evaluates to "TRUE", the value should be set to "DONE" permanently, and not evaluated anymore?

If so, I think you might need to use VBA, as a cell can only contain a formula or a value, but never both at the same time.
You would need something to change your formula into a hard-coded value.

Excel VBA has what is called "event procedures", which are VBA code procedure that run automatically when some event occurs.
For example, the "Worksheet_Change" event procedure is VBA code that runs automatically when a specified range of cells changes.
Initially, that seems like that may work here, but sadly, I do not think it will. The problem is that event procedure is only triggered when cells are manually updated, and not if they are updated by formulas or links.

There is another event procedure called "Worksheet_Calculate", which runs whenever a calculation is triggered on your sheet.
That seems like it might work in this case, but there is one big downside to this. Unlike the "Worksheet_Change" event procedure, it cannot tell which cell was re-calculated, only that SOME cell was re-calculated on your sheet. So it will run against ALL cell's calculations on your sheet. That could be problematic if you have a lot of data/calculations on your sheet, and if your data is constantly changing (your worksheet could be in a constant state of calculation), and might be a bear to work with.

 

[henrique218]

Are you saying that for any particular row, the first time your formula evaluates to "TRUE", the value should be set to "DONE" permanently, and not evaluated anymore?

Yes, this is exactly what I want!

Excel VBA has what is called "event procedures", which are VBA code procedure that run automatically when some event occurs.
For example, the "Worksheet_Change" event procedure is VBA code that runs automatically when a specified range of cells changes.
Initially, that seems like that may work here, but sadly, I do not think it will. The problem is that event procedure is only triggered when cells are manually updated, and not if they are updated by formulas or links.

There is another event procedure called "Worksheet_Calculate", which runs whenever a calculation is triggered on your sheet.
That seems like it might work in this case, but there is one big downside to this. Unlike the "Worksheet_Change" event procedure, it cannot tell which cell was re-calculated, only that SOME cell was re-calculated on your sheet. So it will run against ALL cell's calculations on your sheet. That could be problematic if you have a lot of data/calculations on your sheet, and if your data is constantly changing (your worksheet could be in a constant state of calculation), and might be a bear to work with.

The problem is one entire column (at this moment the size is about 800 rows and everey week will be bigger) is constantly updating. So, maybe Excel will crash.

I read this post: Stop if function to check once it true.

Maybe this works. But I don't know if it's the best way and I'm a little afraid that the people who will use the sheet, will have some problems in another worksheet. Since we need to change configuration in options.

 

[Joe4]

Yes, this is exactly what I want!


The problem is one entire column (at this moment the size is about 800 rows and everey week will be bigger) is constantly updating. So, maybe Excel will crash.

I read this post: Stop if function to check once it true.

Maybe this works. But I don't know if it's the best way and I'm a little afraid that the people who will use the sheet, will have some problems in another worksheet. Since we need to change configuration in options.

I forget how you can manipulate circular references and use iterations to sometimes do things like that.
I think that may work. That may be your best bet.