"ratcheting" a value in Excel

[kenrperry]

In Excel, I am trying to find a way to "ratchet up" the value in a cell. I want the value of the cell to never decrease, but to increase if the value of another cell is higher than the current value of this ratchetable cell. The other cell is automatically updated from the web daily and I am trying to track the highest ever value, thus the need to "ratchet up" the value in the ratcheted cell if the other cell is larger, but to not lower the ratcheted value if the other cell is lower.

ideas?

 

[Michael M]

Hi and Welcome to the Board
Copy this into the sheet module, and adjust ranges to suit

Private Sub worksheet_change(ByVal target As Range)
Set target = Range("B1")
If Range("A1") < target Then Range("A1") = target
End Sub

 

[T. Valko]

In Excel, I am trying to find a way to "ratchet up" the value in a cell. I want the value of the cell to never decrease, but to increase if the value of another cell is higher than the current value of this ratchetable cell. The other cell is automatically updated from the web daily and I am trying to track the highest ever value, thus the need to "ratchet up" the value in the ratcheted cell if the other cell is larger, but to not lower the ratcheted value if the other cell is lower.

ideas?

You can do this with a formula that uses a circular reference.

What version of Excel are you using?

 

[mikerickson]

Perhaps this UDF with a formula like
=RatchetUp(A1)
Note that the Reset argument allows you to reset the value.

Function RatchetUp(testValue As Double, Optional Reset As Boolean = False) As Double
    RatchetUp = WorksheetFunction.Max(Val(Application.Caller.Text), testValue)
    If Reset Then RatchetUp = testValue
End Function

 

[Dryver14]

Biff,

I have 2007 and would be interested in seeing how you get the circular reference to work please

 

[T. Valko]

Biff,

I have 2007 and would be interested in seeing how you get the circular reference to work please

Do this before you enter the formula or you'll get warning messages...

Office button>Excel Options>Formulas>Calculation Options

Select: Enable iterative calculation
Maximum iterations: 1
OK out

Let's assume cell A2 is the input cell.

Enter this formula in B2:

=MAX(B2,A2)

 

[Dryver14]

Thankyou.

Regards,

Kev.

 

[T. Valko]

Thankyou.

Regards,

Kev.

You're welcome!

 

[kenrperry]

Excel 2007

 

[kenrperry]

Thanks - works great and is a great example of KISS