+ Reply to Thread
Results 1 to 4 of 4

How can I cut and paste a values without getting #Ref (sum, running total)

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    How can I cut and paste a values without getting #Ref (sum, running total)

    I am using excel to balance my checking account. I have it setup the way my bank does, by which I mean, with regards to the numbers, two columns--one for money spent or received and another one right next to it with a running total.

    So, G1 would have a number to start things off. Let's say it's 0. F2 would have $30 for me receiving $30. G2 would have =G1+F2, which would be $30. Now, F3 would have -5 for $5.00 that I spent on something and G3 would have =G2+F3, which would be $25.

    If I were to cut the value from F3 and paste it into F2, my G columns gets messed up. I get #Ref starting from the row where I pasted the value (in this case row 2) until the very end.

    How can I prevent this other than copying and pasting, which I hate doing because I am usually rearranging the values to get them in the order that they appear in my checking account or for when I spend in an order different than I originally planned. It's very annoying copying, pasting, and then deleting when cut and paste is so much more convenient.

    Using Indirect and Offset as was mentioned here: http://www.excelforum.com/excel-gene...ll-in-sum.html looks to be the solution, but I am not sure how to implement it. I tried using Indirect, but excel kept on telling me it was an invalid formula

    If this is the solution can someone please instruct me how to go about using them correctly with what I currently have, and if not, please point out what the solution should be.

    Thanks and looking forward to your responses.

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: How can I cut and paste a values without getting #Ref (sum, running total)

    I would go with this in G2 and fill down: =SUM(G1,OFFSET(G2,0,-1))

    - Moo

  3. #3
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: How can I cut and paste a values without getting #Ref (sum, running total)

    Thank you very much Moo. It works great and so far I don't see any problems with it.

    I wish I would have looked into this wayyy sooner.

    Once again, thanks.

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: How can I cut and paste a values without getting #Ref (sum, running total)

    You're welcome, roseuz. Glad to help.

    - Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1