Increase current cell ONLY when different than target cell

[Kemosabe]

I am looking for a way to avoid the circular reference issue. I would like to monitor a cell that is being incremented and decremented so that I can automatically retain the highest and the lowest values that were entered into that specific cell over time. Since the data is only entered into that specific cell and not retained in for example a column, the MAX/MIN option is not usable in this case.

More simply stated, is there a way to put a formula in a specific cell ( A1 ) that will equal the target cell ( B1 ) only when that target cell ( B1 ) is greater than A1?

Thank you.

 

[lenze]

Hi and welcome to the board!!

Probably not with a formula, but you should be able to do it with code. Can you provide more detail as to what you really need? Do you want A1 or B1 to reflect the MIN/MAX?

lenze

 

[Kemosabe]

Hi and welcome to the board!!

Probably not with a formula, but you should be able to do it with code. Can you provide more detail as to what you really need? Do you want A1 or B1 to reflect the MIN/MAX?

lenze

Thanks. Glad to be here.

I am frequently inputting changing values into B1. I would like a cell - A1 - to always retain the highest value of the data that was put into B1 over time. More specifically, I have a spreadsheet that I use to monitor interday fluctuations on certain stocks and it's impact to the bottom line. I input values and as days/weeks pass, I'd like to know the highest and lowest value that was attained over the entire timeframe. In short and to answer the question directly, I want B! to always reflect the MAX that was ever put into A1. Needles to say, I'll modify the process for B2 to reflect the MIN.

Does this clarify the issue?

 

[lenze]

If I understand you, this should work!!

In the WorkSheet module(Right click on the sheet tab and choose "View Code"), paste this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Not Application.WorksheetFunction.IsNumber(Target) Then Exit Sub
If Target > Range("$B$1") Then Range("$B$1") = Target
If Target < Range("$B$2") Then Range("$B$2") = Target
End Sub

Now when a value is entered in A1, if it is greater than B1, B1 will be changed to the new value. Likewise, if it is less than B2, B2 will be changed to reflect the new value.

You must, of course, save the file to make the change permanent>

HTH
lenze

 

[Yogi Anand]

Hi kemosabe:

In line with Lenze's thinking ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B1") And Target.Count = 1 And Target > [a1] Then [a1] = Target
End Sub

 

[Scott Huish]

Yogi:

That won't avoid the Type Mismatch if more than one cell is changed.
Also I added Application.EnableEvents to avoid the code calling itself again.

Kemosabe:

Perhaps:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Application.EnableEvents = False
    If Target > Range("A1") Then Range("A1") = Target
Application.EnableEvents = True
End Sub

 

[Kemosabe]

If I understand you, this should work!!
lenz

Thanks for the responses. They are truly appreciated.

The truth be told, my dilemma is now compounded. I opened a new workbook and followed (or thought I did) your instructions Lenz but could not achieve the end result. I was able to choose “View Code” and paste the 7 lines of code inclusively from “Private Sub … “ to “End Sub” – as well as trying the other alternatives that were offered by others -into an area that was headed by tabs (General) and “Worksheet Change” but I could not get any further because I lack the knowledge for implementing and executing macros. I even took an online Microsoft Office Excel training course for macros hopeful that it would fill in the gaps. You just can’t get a good enough command of Visual Basic overnight. . Although I have used Excel for a while, I do not have any experience with Visual Basic for Applications and have never written a macro.

Understanding that the purpose of MrExcel.com is to offer help and not to teach classes in Excel, hopefully you can give me some direction/pointers that will get me to the promised land regarding my specific Excel problem.

Thanks you all for the help .......... /herb

 

[lenze]

Try this for my code.
Open a new workbook and Rightclick the Sheet1 tab. Select "View Code".
Paste the code I posted in the white panel. Close the Visual Basic Editor(ALT+Q) Now enter 10 In B1, In B2 enter 5.
Now begin inputting values in A1. B1 or B2 should update if the value is outside the range of B1,B2. If you enter 11 in A1, B1 will change to 11. If you enter 2, B2 will become 2. The changes are automatic, but you MUST have macros enabled. I tested this and it DOES work.

Let me know if you can't get it to work.

lenze

 

[Boller]

If Target.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Target = Range("B1") And Target.Count = 1 And Target > [a1] Then [a1] = Target
If Target.Address <> "$B$1" Then Exit Sub


The sort of code per above is often suggested on this board, but I think it's poor.

It doesn't cover situations where the cell in question is changed but the target consists of more than one cell?

Why not use the MS suggested syntax :-

If Not Intersect(Target,[A1]) Is Nothing Then

 

[Scott Huish]

Since the data is only entered into that specific cell

Given the above, it should work fine.

But since you brought it up, you don't have to check if there are more than 1 cells in Target, if you are checking for the address of a single cell, as it won't return a single cell address if more than one cell has been changed.

Why not use the MS suggested syntax :-

If Not Intersect(Target,[A1]) Is Nothing Then

I do quite often actually, although I usually set it to a range and then exit if the range equals nothing, I think it's cleaner, plus it will give you a range to work with.