Updating, or not, a cell in Excel

[rickgalty]

How do I use an "If" to either copy the contents of a cell into another cell, OR leave the destination cell untouched, depending on a certain logical test. I see the syntax of the "If" fnction is "If(logical test, value if true, value if false)", but what if I don't want a 'value if false'? If the test is false I want to leave what was in there untouched.

Thanks, Richard

 

[SkipVought]

Hi,

Once you have a formula in a cell, it is NOT UNTOUCHED.

A formula ALWAYS refers to a range externla to itself, or it is identified as having a CIRCULAR REFERENCE, which is usually a bad thing.

Therefore, a cell containing a formula cannot be untouched.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[rickgalty]

OK, I didn't make myself clear, sorry.

Let's say "A1" contains a number. "A2" and "A3" contain different numbers. If A2 is bigger than A3 then I want to change the value of A1, otherwise I want to leave A1 as it is.

Any suggestions?

Richard

 

[SkipVought]

You were perfectly clear! That can only be done via code, not with native Excel functionality. The code would fire on the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
   set rng = application.intersect(target,application.union([a2],[a3]))
   if not rng is nothing then
      if [a2]>[a3] then
         [a1] = "some other value"
      end if
   end if
End Sub

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[byundt]

It is possible to do what you want without VBA, but I really don't recommend it.

1) Open the Tools...Options menu item, go to the Calculations tab and set the maximum number of Iterations to 1
2) Put your desired formula in cell A1. If you were capturing a momentary high for cell A2, the formula might look something like this:
=IF(A2>A3,A2,A1)

Anybody who uses the workbook needs to set the Iterations to 1--which is one good reason not to solve your problem this way. They'll get a warning message about circular references if they haven't taken this step. Another good reason is the fact that every other open workbook will also have iterations turned on.

Brad