1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

moving areas of cells in Excel 2007

Discussion in 'Business Applications' started by rosmari, Oct 4, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. rosmari

    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?
     
  2. slurpee55

    slurpee55

    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?
     
  3. rosmari

    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!
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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 :)
     
  5. slurpee55

    slurpee55

    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!!!
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Two things:
    First: Congratulations!!!
    Second: Prepare to die!!!

    ROFL!! :D
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Aaaaargh! :D
    A challenge, just when I am swamped at work!! :eek: (excuses, excuses!)
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I would become a post whore for nobody else except you, Loche. If that is of any consolation. ;)
     
  9. rosmari

    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!
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. :)
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Awwww, I'm touched...(been told that before, come to think of it!) (y)
     
  12. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/755942

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice