Excel formula to show and update cell info

[dmccallum]

I put the company's checkbook in Excel with these headers
(cell A1) (cell B1) (cell C1) (cell D1) (cell E1) (cell F1)
Date Descrip CK# Prop # Acct Code Amt

Everytime the Acct Code = 130 I would copy and paste the entire row from A-F to another sheet titled All_130s and total the sheet up with subtotals for property #. I have tried numerous formulas but I can't see how to just display the cells (without the cut and paste) so that they are still linked and updateable from the original sheet. What I finally came up with will work but I still think there is a better way than:

=+IF('April 2000'!E20=A1,'April 2000'!A20,"&quot

This has to be copied and modified into each cell and that's no easier than manually copying and pasting. I tried using the advanced filter (failure) and query (machine locks up).

 

[Guest_imported]

I assume that you are not simply trying to sum the amounts. If you were, DSUM would work. If you want to create linked copies to every row that contains "130" in column E the following code should work.

Sub whatever
Sheets("Sheet1&quot.Select
Range("A2&quot.Select
Sheets("April 2000&quot.Select
Range("E2&quot.Select
For Each xRow In ActiveSheet.UsedRange
If ActiveCell.FormulaR1C1 = "130" Then
Selection.EntireRow.Copy
Sheets("Sheet1&quot.Select
ActiveSheet.Paste Link:=True
ActiveCell.Offset(1, 0).Range("a1&quot.Select
Application.CutCopyMode = False
End If
Sheets("April 2000&quot.Select
ActiveCell.Offset(1, 0).Range("a1&quot.Select
Next xRow
End Sub