Contact Forum Editor

Send an email to our Forum Editor:

PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom

It's free to register, to post a question or to start / join a discussion

View Forum Rules | Contact Forum Editor

 

excel puzzle rather than problem

marvin42

Likes # 0

Posted December 9, 2011 at 1:20PM

I have a large speadsheet which gives me a picture of my bank balance throughout the year. There are 4 columns per month - A=date,B=Item, C=amount, D=balance (e.g. D4=D3-C4). These 4 columns are then repeated for each month. All of my direct debits and standing orders are entered for the year and it takes my credit card balances from another sheet. Any credits I enter as a negative so that the same formula works throughout.

Puzzle 1: If I cut an entry (e.g. cells B4 and C4) and then paste them (e.g. to B12 and C12) I get a #REF error in the balance column for everything from D12 onwards. Copy and paste and then delete the original entry works without any problem.

Puzzle 2: I have conditional formatting so that all negative numbers are shown in red. When I copy and paste (e.g. B4 and C4) the conditional formatting remains but the colour of the negative cell changes randomly - sometimes orange, green or blue etc.

As I said, not a major problem but I'm curious to know why this happens?

Like this post
stlucia2

Likes # 0

Posted December 10, 2011 at 8:07AM

I use a similar spreadsheet, and have noticed the same as you -- I have to cut and paste, then separately delete, if I want to move an entry without getting an error in the balance column. Be interesting to know if there's a simple, elegant, way around it.

Like this post
mgmcc

Likes # 0

Posted December 10, 2011 at 9:03AM

"I get a #REF error in the balance column for everything from D12 onwards."

Take the formula in D11 and drag it down through the "cut" cells to restore the correct formula down the column.

Like this post
mgmcc

Likes # 0

Posted December 10, 2011 at 9:06AM

I suspect I may have misunderstood what you've actually done, so disregard that. :(

Like this post
Terry Brown

Likes # 0

Posted December 10, 2011 at 9:08AM

When you built the system, you set up the first column and then used extended down to as many as you needed. When you copy and paste, what you are actually copying is not the figures but the reference to the formula. To prove this point do the following:

Open the spreadsheet and press the CTRL key and the key to the left of the '1' key on the main keyboard and you see the functions instead of the numbers. Press again to return to normal.

Puzzle 2 - I'm not sure why that happens.

Terry

Like this post
marvin42

Likes # 0

Posted December 10, 2011 at 10:27AM

stlucia2 Thanks for that - I'm glad it's not me!!

Terry Brown I understand that but what I don't get is why copy and paste works but cut and paste doesn't.

Like this post
marvin42

Likes # 0

Posted December 10, 2011 at 2:08PM

mgmcc Actually, your suggestion works perfectly. I still can't understand why this happens.

Like this post

Reply to this topic

This thread has been locked.



Check out PC Advisor's other tech forums

Send to a friend

Email this article to a friend or colleague:

PLEASE NOTE: Your name is used only to let the recipient know who sent the story. Both your name and the recipient's name and address will not be used for any other purpose.