[rosmari]

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?

[slurpee55]

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]

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!

[Zack Barresse]

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

[slurpee55]

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!!!

[Zack Barresse]

Originally Posted by slurpee55

And Zack, I have more posts than you! Time to catch up!!!

Two things:
First: Congratulations!!!
Second: Prepare to die!!!

ROFL!!

[slurpee55]

Aaaaargh!
A challenge, just when I am swamped at work!! (excuses, excuses!)

[Zack Barresse]

I would become a post whore for nobody else except you, Loche. If that is of any consolation.

[rosmari]

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!

[Zack Barresse]

Originally Posted by rosmari

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.

[slurpee55]

Originally Posted by Zack Barresse

I would become a post whore for nobody else except you, Loche. If that is of any consolation.

Awwww, I'm touched...(been told that before, come to think of it!)