Update on change

[myleslawrence]

I'm trying to figure out how to update the value on sheet1 in column F, row
5 with the value on sheet2 column F, row 20 whenever that value on sheet3
changes. If someone would give a tip I'd appreciate it.

 

[Tom Ogilvy]

In Sheet1!F5 put in a formula like

=Sheet2!F20

This would update whenever a calculation occured.

Otherwise you would have to use a worksheet_change event in sheet 3.

What cell in Sheet3 would trigger the change

Assume F20 in Sheet3

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Address = "$F$20" then
worksheets("Sheet1").Range("F5").Value = _
Worksheets("Sheet2").Range("F20").Value
End If
End Sub

 

[myleslawrence]

Thanks Tim.
What calls the Worksheet_Change subroutine? If in your example, F20
triggers the change, how do I get that to happen and call the sub?

 

[myleslawrence]

If you're feeling generous,
Rather than update worksheets("Sheet1").Range("F5").Value =
Worksheets("Sheet2").Range("F20").Value
I really would like to update worksheets("Sheet1").($some row
number$5).Value = Worksheets("Sheet2").Range("F20").Value
where that (some row number) in Sheet1 has the name of the Sheet from the
target sheet in the first column.

So if Sheet1 has "Sheet2", "Sheet3", "Sheet4" in cells A1, A2 and A3 then I
want to update cell F2 with the contents of Sheet2:F20 and I want to update
F3 with the contents of Sheet3:F20 and so on. Can you help me with this? I'd
really appreciate it.

 

[Tom Ogilvy]

changing the value in F20 of sheet 3, either by editing the cell or by code,
will fire the macro if you place it in the sheet module of sheet3

right click on the sheet tab and select view code, then put it in that
module.

see Chip Pearson's site on events

http://www.cpearson.com/excel/events.htm

 

[Tom Ogilvy]

for i = 1 to 3
Worksheets("Sheet1").cells(i+1,"F").value = _
worksheets(cells(i,"A").Value).Range("F2")
Next i

You can adapt that with the change event I gave you if it is to be triggered
by some other cell being changed.