I use Excel 2003.
I import changing data into one cell 'D7' in the form of a number. I use
information from other cells 'R7,T7,U7' to give me a target, and once the
target is achieved, the text "Yes" is automatically printed in another cell
'M7'.
I have been able to make this work ok using the formula below, but
unfortunatley once the figure rises above the target again, the text
disappears.
Does anyone know of a way that I can make the text remain in the cell once
the target is achieved until I manually reset it.

=IF(AND(R7="H",(D7<=((T7-U7)/2+U7))),"Yes","")

I would ppreciate any help.
Cheers
cliff18

Try this on a spare copy

Right-click the sheet tab choose "View Code"
Copy n paste the sub below into the code window (white space on right)
Press Alt+Q to get back to Excel
Test it out ...

'------
Private Sub Worksheet_Calculate()
If Range("M7").Value = "Yes" Then
Range("M7").Value = Range("M7").Value
End If
End Sub
'-----

And to re-instate the formula in M7 later (when it doesn't evaluate to
"Yes"), you could install the sub below in a regular module*, then run it in
Excel, via say, the Macro dialog (press Alt+F8)
*press Alt+F11 to go to VBE, click InsertModule, copy n paste

Sub Put_In_M1()
Range("M7").FormulaR1C1 = _
"=IF(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8]))),""Yes"","""")"
End Sub
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"cliff18" wrote:
I use Excel 2003.
I import changing data into one cell 'D7' in the form of a number. I use
information from other cells 'R7,T7,U7' to give me a target, and once the
target is achieved, the text "Yes" is automatically printed in another cell
'M7'.
I have been able to make this work ok using the formula below, but
unfortunately once the figure rises above the target again, the text
disappears.
Does anyone know of a way that I can make the text remain in the cell once
the target is achieved until I manually reset it.
=IF(AND(R7="H",(D7<=((T7-U7)/2+U7))),"Yes","")

Thanks Max, what you gave me worked perfectly, including reloading the
formula with the Macro!
The only thing I didn't mention is that I would like this formula to work
all the way down from row 7 (as in the formula I sent) down to row 20. I
thought I could work it out myself with what you gave me, but unfortunatley I
am just getting errors as I change the range as below.
'------
Private Sub Worksheet_Calculate()
If Range("M7:M20").Value = "Yes" Then
Range("M7:M20").Value = Range("M7:M20").Value
End If
End Sub
'-----
Sorry to ask again but is it possible to get this to work in this way.
--
Cheers
cliff18


"Max" wrote:

Try this on a spare copy

Right-click the sheet tab choose "View Code"
Copy n paste the sub below into the code window (white space on right)
Press Alt+Q to get back to Excel
Test it out ...

'------
Private Sub Worksheet_Calculate()
If Range("M7").Value = "Yes" Then
Range("M7").Value = Range("M7").Value
End If
End Sub
'-----

And to re-instate the formula in M7 later (when it doesn't evaluate to
"Yes"), you could install the sub below in a regular module*, then run it in
Excel, via say, the Macro dialog (press Alt+F8)
*press Alt+F11 to go to VBE, click InsertModule, copy n paste

Sub Put_In_M1()
Range("M7").FormulaR1C1 = _
"=IF(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8]))),""Yes"","""")"
End Sub
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"cliff18" wrote:
I use Excel 2003.
I import changing data into one cell 'D7' in the form of a number. I use
information from other cells 'R7,T7,U7' to give me a target, and once the
target is achieved, the text "Yes" is automatically printed in another cell
'M7'.
I have been able to make this work ok using the formula below, but
unfortunately once the figure rises above the target again, the text
disappears.
Does anyone know of a way that I can make the text remain in the cell once
the target is achieved until I manually reset it.
=IF(AND(R7="H",(D7<=((T7-U7)/2+U7))),"Yes","")

Tinkered around, this seems ok:

Private Sub Worksheet_Calculate()
Set c = Range("M7:M20")
For Each i In c
If i.Value = "Yes" Then
i.Value = i.Value
End If
Next
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"cliff18" wrote:
Thanks Max, what you gave me worked perfectly, including reloading the
formula with the Macro!
The only thing I didn't mention is that I would like this formula to work
all the way down from row 7 (as in the formula I sent) down to row 20. I
thought I could work it out myself with what you gave me, but unfortunatley I
am just getting errors as I change the range as below.
'------
Private Sub Worksheet_Calculate()
If Range("M7:M20").Value = "Yes" Then
Range("M7:M20").Value = Range("M7:M20").Value
End If
End Sub
'-----
Sorry to ask again but is it possible to get this to work in this way.

If I could tinker like that I'd spend less time here, that's for sure!
After what you've given me, I simply changed the range in the macro to
("M7:M20") for reloading the formula and it all seems perfect!
--
Thanks Max, I had no idea.
cliff18


"Max" wrote:

Tinkered around, this seems ok:

Private Sub Worksheet_Calculate()
Set c = Range("M7:M20")
For Each i In c
If i.Value = "Yes" Then
i.Value = i.Value
End If
Next
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"cliff18" wrote:
Thanks Max, what you gave me worked perfectly, including reloading the
formula with the Macro!
The only thing I didn't mention is that I would like this formula to work
all the way down from row 7 (as in the formula I sent) down to row 20. I
thought I could work it out myself with what you gave me, but unfortunatley I
am just getting errors as I change the range as below.
'------
Private Sub Worksheet_Calculate()
If Range("M7:M20").Value = "Yes" Then
Range("M7:M20").Value = Range("M7:M20").Value
End If
End Sub
'-----
Sorry to ask again but is it possible to get this to work in this way.

Sorry Max, but I found when I shut the application down and restart it I get
a 'Run-time error '13';' I can simply end it and it carries on so it's not a
big problem I guess.
I'll spend more time with it later.
Thanks again mate!
--
Cheers
cliff18


"cliff18" wrote:

If I could tinker like that I'd spend less time here, that's for sure!
After what you've given me, I simply changed the range in the macro to
("M7:M20") for reloading the formula and it all seems perfect!
--
Thanks Max, I had no idea.
cliff18


"Max" wrote:

Tinkered around, this seems ok:

Private Sub Worksheet_Calculate()
Set c = Range("M7:M20")
For Each i In c
If i.Value = "Yes" Then
i.Value = i.Value
End If
Next
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"cliff18" wrote:
Thanks Max, what you gave me worked perfectly, including reloading the
formula with the Macro!
The only thing I didn't mention is that I would like this formula to work
all the way down from row 7 (as in the formula I sent) down to row 20. I
thought I could work it out myself with what you gave me, but unfortunatley I
am just getting errors as I change the range as below.
'------
Private Sub Worksheet_Calculate()
If Range("M7:M20").Value = "Yes" Then
Range("M7:M20").Value = Range("M7:M20").Value
End If
End Sub
'-----
Sorry to ask again but is it possible to get this to work in this way.