Dynamic link to workbook
Through the UI, just copy an entire row (say for art 11111 to the
bottom of your summary table then select the new row and Edit / Replace
/ 11111 / with: 11134 / Replace All.
If you wanted to use a macro, you could detect a change in column 1 of
the worksheet and copy the row above, and then do the replace, as
above. Something like this, in the worksheet's module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Cells.Count = 1 And Not
IsEmpty(Target) And Target.Row 1 Then
' copy down the formulas from the row above
With Target.Offset(, 1).Resize(, Columns.Count - 1)
.FillDown
.Replace Target.Offset(-1).Value, Target.Value, xlPart
End With
End If
End Sub
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
|