Use the Workbook_SheetChange event.
Let us say:
We have a workbook (Book2) with 4 sheets - Sheet1, sheet2, Sheet3, Sheet4
Sheet1 contains a critical cell (A1) which we wish to track and which has the formula =Sheet3!A1
Sheet2 col A is where we wish to poke changing values for Sheet1!A1
When Sheet3!A1 changes (Sheet3!A1 is NOT a formula), the formula in Sheet1!A1 will update the value but will not trigger the WorksheetChange event.
We will devise a way to force the WorksheetChange event for Sheet1!A1 when the cell on which it is dependent (Sheet3!A1) changes.
In sheet4:
Define a range name "Addresses" which refers to Sheet4!A:B (cols A and B)
In col A input the (full) addresses of all precedent cells of all formula cells which you want to track and in col B put the corresponding (full) address for the formula cell itself. See the workbook below where the cells in our example are input:
Sheet4
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 126px"><COL style="WIDTH: 191px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>[Book2]Sheet3!$A$1</TD><TD>[Book2]Sheet1!$A$1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
In the ThisWorkbook module put the following code:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range
For Each cell In Target.Cells
Addr = cell.Address(True, True, xlA1, True)
Mtch = 0
On Error Resume Next
Mtch = WorksheetFunction.Match(Addr, Range("Addresses").Columns(1), 0)
On Error GoTo 0
If Mtch > 0 Then
Set Rng = Range(Range("Addresses").Cells(Mtch, 2))
If Rng.HasFormula Then Rng.Formula = Rng.Formula
End If
Next
End Sub
Notice the code senses change in Sheet3!A1, locates it with Addresses col 1 in row 1, reads the address in col 2 of this row and RE-INPUTS the formula in that cell. This re-input triggers the worksheet_change event which runs the code I posted earlier...
Whew!!!!
