Increase current cell ONLY when different than target cell

Kemosabe

New Member
Joined
Jul 27, 2007
Messages
8
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Jul 27, 2007
Messages
8
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

Legend
Joined
Feb 18, 2002
Messages
13,690
If I understand you, this should work!!

In the WorkSheet module(Right click on the sheet tab and choose "View Code"), paste this
Code:
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi kemosabe:

In line with Lenze's thinking ...
Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Code:
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

New Member
Joined
Jul 27, 2007
Messages
8
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,657
Messages
5,931,261
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top