Excel Cut and Paste

Factual Questions
1

I have an excel spreadsheet question, with layout as follows:
It has columns A B C and D. A is date, B is Category (Spending or Paycheck), C is amount of credit or debit and D is running balance.
D is in the format of =D2-C3.
For example,
D3 = D2-C3
D4 = D3-C4
D5 = D4-C5

Every time I select a block of A B C, cut and paste, the references in column D hose up, so if I move the contents of columns A B and C down 14 rows, D3 will be =D2-C15.

How do I keep my “column D” from getting hosed up like this?

2

The only way I’ve found to solve it is to copy and paste, and then go back and delete the original block.

3

Tokyo:
Wow. That’s so simple I never even thought of it. I’ll go give that a try.
… 1 minute later …
Didn’t work. Either I described my problem wrong, or you misinterpreted me.
Hmmmmmmmmmmmmmmmmm.

4

Just a question–are you entering the formula for column D in lower case?

5

Have you tried any of the options from the Paste Special dialog (under the Edit menu)?

6

Use paste special values.

7

Here’s something you can try. It’s a bit kludgy, but it seems to do the job.

Put this formula in EVERY cell in your D column (except for the top one, which I presume has an opening balance or something):

=INDIRECT(“R[-1]C”, FALSE)-INDIRECT(“RC[-1]”, FALSE)

This gives it the value of the cell immediately above, minus the value of the cell immediately to the left. Putting the references in strings makes it immune to Excel playing around with it.

8

Sorry wont work with cut only copy and paste.

9

Have you tried cutting A, B, C AND D then using “insert cut rows” where you’re going to paste (instead of just “paste”)?

Then D would carry over with the C values it was associated with, and the other D’s will line up.

I admit I suck at Excel but I’ve been doing alot of cutting and pasting lately and it seems to work for me.

10

Just to clarify: when I said EVERY cell in your D column, I didn’t mean the entire column, but only those that you want the running total in (which may or may not be the entire column). I emphasised EVERY because that exact formula can be used in all those cells without having to change it.

11

Thank you. Your solution is both elegant and worked perfectly.

12

YEAHHH!!! WHO DA MAN!!!

ahem

Glad to be of assistance. :slight_smile: