Freezing(?) cells

[Mark Long]

Hello,
I need to set up my worksheet so that if H20<4000 H24 shows "A Charge"
but does not change back once H20 >4000 again, so that when I produce a
printout I'll know if there has been a charge incurred at some point. I have
it set up to work to the point where H24 shows "A Charge", but as soon as I
get back above 4k, it goes back to "No Charge". How can I freeze (right
term?) H24 so that it will show up correctly on a printout should the
conditions have arisen?

 

[jeff]

Hi,

I think you can only do this with VBA (trying with
a formula gets you a circular reference).

right click on the tab name, select 'view code'
paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H24").Value <> "A Charge" Then If Range
("H20").Value < 4000 Then Range("H24") = "A Charge"
End Sub

(lines 2 & 3 should be on one line) YOu can modify/
expand the cell references to include other rows/columns.

jeff

 

[Mark Long]

Hi,

I think you can only do this with VBA (trying with
a formula gets you a circular reference).

right click on the tab name, select 'view code'
paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H24").Value <> "A Charge" Then If Range
("H20").Value < 4000 Then Range("H24") = "A Charge"
End Sub

(lines 2 & 3 should be on one line) YOu can modify/
expand the cell references to include other rows/columns.

jeff

Hello Jeff,
Well waddya know that works a treat. Much obliged, Sir! I knew it was
probably going to need vba, but wouldn't know where to start with it, so
thanks.
Mark

 

[daniel chen]

Try this one
=IF(H20="reset","",IF(H20<4000,"A charge",H24))