Hi Ron
this can not be done with a formula, you need to use VBA code ... this can
be done "automatically" via a Worksheet_Change procedure, e.g.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
Range("A3").Copy
Range("A3").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Application.EnableEvents = True
End Sub
---
this does the edit / paste special - values operation of cell A3 if cell A1
changes and if it is not blank. However, please note, that if A1 changes a
second time the value in A3 will not change again.
---
to use the code, right mouse click on the sheet tab of where the cell is and
choose view code - on the top left hand side of the VBE window you should
see your workbook's name in bold with sheet1 or whatever sheet you right
mouse clicked on highlighted (if you can't see this you probably need to
choose view / project explorer)
copy & paste the above code into the right hand side of the screen ...
changing A1 & A3 to whatever cells you want.
use Alt & F11 to get back to the worksheet & test it out.
Please note, if you're ever testing code that changes things, do it on a
copy of the workbook not on the original one - just in case!
Hope this helps
Regards
JulieD
