moving areas of cells in Excel 2007

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rosmari

Thread Starter
Joined
Sep 7, 1999
Messages
487
I have a spreadsheet with some blank rows. I want to move up the rows below so as to eliminate those blank rows. Most cells on that spreadsheet have formulas in them.

I can move a row by cutting and pasting, and the formulas adjust. But elsewhere on the sheet, I get #REF! in cells which depend on formulas outside the area.

I had LOTUS before, and there I could simply click /m, and that would move any area I had highlighted to wherever I designed it to go - with no detrimental effect on any other cells with formulas.

So how can I do that in Excel 2007?
 
Joined
Oct 20, 2004
Messages
7,837
Are your formulas that give you errors absolute or dynamic?
Can you give a bit more information on where your formulas are (as opposed to what you cut) and what the formula(s) are?
 

rosmari

Thread Starter
Joined
Sep 7, 1999
Messages
487
I don't know what you mean by absolute or dynamic.

The formulas all refer to other cells, e.g. "+A1*B2", or they just call on other cells, e.g. if I want to copy the value of BB25 into F20, I enter into F20 "+BB25". This all works very well until I try to move a section, at which time the latter type of cell change to #REF!
 
Joined
Jul 25, 2004
Messages
5,458
The reference in the formula turns to #REF! when that dependent is deleted. This is why it is best to not delete or insert anything. I'd recommend deleting all your blank rows, even if that means rebuilding all of your formulas and their dependencies. It will be a lot more work up front but it's worth it.

And you don't need to preceed your cell references with +, unless you are actually adding them. So "+BB25" can just be "=BB25", and "+A1*B2" can become "=A1*B2". No need for the additional coercion.

HTH :)
 
Joined
Oct 20, 2004
Messages
7,837
Absolute references are of the type =$B$2, as opposed to =B2.
What that does is, no matter what you may do to your worksheet in terms of inserting/deleting rows or columns, that formula would always look at the cell in column C and row 2 - otherwise, if you say, inserted a column between A and B, it would shift to C2. With an absolute it would look at the new B2.
And Zack, I have more posts than you! Time to catch up!!!
 
Joined
Oct 20, 2004
Messages
7,837
Aaaaargh! :D
A challenge, just when I am swamped at work!! :eek: (excuses, excuses!)
 
Joined
Jul 25, 2004
Messages
5,458
I would become a post whore for nobody else except you, Loche. If that is of any consolation. ;)
 

rosmari

Thread Starter
Joined
Sep 7, 1999
Messages
487
in that case, they are all dynamic references.

This is a spreadsheet of my investments, and they change from time to time, so I have to have an easy way to move sections around. This was so easy with Lotus; surely Excel 2007 must have a simple way of doing it!
 
Joined
Jul 25, 2004
Messages
5,458
in that case, they are all dynamic references.

This is a spreadsheet of my investments, and they change from time to time, so I have to have an easy way to move sections around. This was so easy with Lotus; surely Excel 2007 must have a simple way of doing it!
As I said before, you're shooting yourself in the foot if you think you can get away with blank rows/columns/cells and having a data structure that you can "move around", either by using cut/insert/delete/whatever. It's not a good idea to do that. If you can set it up without having to do that, you'll be much better off. Feel free to post a sample of your file if you'd like some input on it. Take out any sensitive data first, of course. :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Top