CODEWITHSUNDEEP
excelcell
jzzarda
jzzarda

Reputation: 3

Excel IF a cell value is bigger then previous value of the same cell

I would like to put a value to A1 and when I changed A1 I want to keep the first entered value in another cell.

Would that be possible ?

Thanks

Upvotes: 0

Views: 1549

Answers (2)

J.Ty.
J.Ty.

Reputation: 16

Here is a solution using formulas, only. It requires that you switch the iterative calculations on in Excel options. You can set it to 1 iteration.

You only need to enter the following formula into B1:

=IF(ISBLANK(C1),IF(ISBLANK(A1),B1,IF(B1="",A1,B1)),"")

A1 is the cell whose value is to be remembered, B1 is the memory location, C1 is the reset button.

  • As long as C1 is empty, B1 holds the very first value entered into A1.
  • When you enter any value in C1, B1 becomes empty and blocked in this state.
  • You can then delete the content of C1, and B1 will become responsive again. It will "catch" the first value present A1 and keep it until you reset it again.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Insert the following event macro in the worksheet area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, N As Long
    Set A = Range("A1")
    If Intersect(Target, A) Is Nothing Then Exit Sub
    If Cells(1, 2).Value = "" Then
        N = 1
    Else
        N = Cells(1, 2).End(xlUp).Row + 1
    End If

    Application.EnableEvents = False
    A.Copy Cells(N, "B")
    Application.EnableEvents = True
End Sub

Every time you enter a value in A1, that value will be recorded in the first availab1e cell in column B

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Upvotes: 2

Related Questions