Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update on change
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update on change
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 -- Regards, Tom Ogilvy "myleslawrence" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update on change
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? "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "myleslawrence" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update on change
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" wrote in message ... 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 -- Regards, Tom Ogilvy "myleslawrence" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update on change
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 -- Regards, Tom Ogilvy "myleslawrence" wrote in message ... 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? "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "myleslawrence" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update on change
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. -- Regards, Tom Ogilvy "myleslawrence" wrote in message ... 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" wrote in message ... 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 -- Regards, Tom Ogilvy "myleslawrence" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbook change and update | Excel Worksheet Functions | |||
change the name of a worksheet/update VBA code | Excel Discussion (Misc queries) | |||
How to update cell on change | Excel Discussion (Misc queries) | |||
How do I get one worksheet to update when others change? | Excel Worksheet Functions | |||
Change Source / Update Now buttons not available | Links and Linking in Excel |