A formula can only update the value in the cell that holds it.



davidhub wrote:

--

Dave Peterson

On Tue, 5 Jul 2005 05:00:01 -0700, "davidhub"
wrote:

In the cell that you wish to change (e.g. A2) enter a formula that refers to
the cell with the formula (e.g. A1)

For example, in cell A2 enter:

=A1

If having a formula in A2 is not acceptable, then you will need to use a VBA
solution. Using an event macro, you can check the contents of A1 and write an
appropriate number into A2.

To enter the VB routine, right click on the worksheet tab and select View Code.

Then paste the code below into the window that opens.

Make the appropriate changes in your cell reference assignments.

==============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellToBeUpdated As Range
Dim CellToCheck As Range

Set CellToBeUpdated = Range("A2")
Set CellToCheck = Range("A1")

CellToBeUpdated.Value = CellToCheck.Value

End Sub
===========================


--ron

Just a heads up on a potential problem with the code suggested.

If you modify the macro slightly to illustrate the problem and enter a
single value in any cell, you will see, that it runs recursively:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellToBeUpdated As Range
Dim CellToCheck As Range
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
Set CellToBeUpdated = Range("A2")
Set CellToCheck = Range("A1")

CellToBeUpdated.Value = CellToCheck.Value

End Sub

Last number for cnt on a single cell entry was 228 (so Excel senses the
problem and corrects after 228 runs).

to prevent this, you need to suppress events


Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellToBeUpdated As Range
Dim CellToCheck As Range
On Error goto ErrHandler:
Set CellToBeUpdated = Range("A2")
Set CellToCheck = Range("A1")
Application.EnableEvents = False
CellToBeUpdated.Value = CellToCheck.Value
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Ron Rosenfeld" wrote in message
...
On Tue, 5 Jul 2005 05:00:01 -0700, "davidhub"
wrote:

In the cell that you wish to change (e.g. A2) enter a formula that refers
to
the cell with the formula (e.g. A1)

For example, in cell A2 enter:

=A1

If having a formula in A2 is not acceptable, then you will need to use a
VBA
solution. Using an event macro, you can check the contents of A1 and
write an
appropriate number into A2.

To enter the VB routine, right click on the worksheet tab and select View
Code.

Then paste the code below into the window that opens.

Make the appropriate changes in your cell reference assignments.

==============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellToBeUpdated As Range
Dim CellToCheck As Range

Set CellToBeUpdated = Range("A2")
Set CellToCheck = Range("A1")

CellToBeUpdated.Value = CellToCheck.Value

End Sub
===========================


--ron

On Tue, 5 Jul 2005 09:45:06 -0400, "Tom Ogilvy" wrote:

Just a heads up on a potential problem with the code suggested.

Thanks for pointing that out. I usually do remember to DisableEvents.
Brainf&&t this time around.


--ron