Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel formula to show and update cell info

Excel formula to show and update cell info

Excel formula to show and update cell info

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,"")

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).

RE: Excel formula to show and update cell info

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("April 2000").Select
For Each xRow In ActiveSheet.UsedRange
    If ActiveCell.FormulaR1C1 = "130" Then
        ActiveSheet.Paste Link:=True
        ActiveCell.Offset(1, 0).Range("a1").Select
        Application.CutCopyMode = False
    End If
    Sheets("April 2000").Select
    ActiveCell.Offset(1, 0).Range("a1").Select
    Next xRow
End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close