Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Inserting cells without affecting formulas

    Hi,

    (I've Excel 2007)

    Is it possible to "switch off" the excel feature that automatically keeps formulas correct when you insert cells?

    Column A & B has a list of pre-planned work like this:

    12:00 13:00
    15:30 16:40

    This list is copied to a timesheet (for printing).

    In the above example, if I'm called out from 13:30 to 13:50, I'd like to INSERT a row at the appropriate place on the list. Excel then alters the
    =A1 =B1
    =A2 =B2
    on my timesheet to give:
    =A1 =A1
    =A3 =B3

    This happens even if I use absolute references ($).

    This spreadsheet is fully developed, so I'd happily switch off this "tracking" feature.

    Thanks,
    Jim MacLeod
    Shetland Isles

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    As long as you don't insert columns, you can use INDEX:
    =INDEX(A:A,1)
    =INDEX(A:A,2)
    and so on.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Absolute/relative would not be expected to affect this behavior. You have linked to cell location and if that cell is moved excel adjusts for this. If you truly want to lock a reference to a position of the cell and have that reference ignore, moving, inserting, deleting, use indirect function:
    =Indirect("A1")

    Will always refer to A1 even if columns are inserted...

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •