+ Reply to Thread
Results 1 to 6 of 6

Keeping cell references in formula the same when cells are inserted

  1. #1
    Registered User
    Join Date
    09-14-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    3

    Keeping cell references in formula the same when cells are inserted

    Hi - I have a problem that I thought would be solved by using absolute references, but it's not. I have a spreadsheet that looks like this:

    __A _______B_______C
    1 Alberts Alberts =IF(NOT(A1=B1),"different","")
    2 Alberts Alberts =IF(NOT(A2=B2),"different","")
    3 Brooks Alberts =IF(NOT(A3=B3),"different","")
    4 Brooks Brooks =IF(NOT(A4=B4),"different","")

    As you would expect, C3 displays "different", all other cells in column C are blank. So far so good. However, what I want to do, now that I've identified the "different" row, is to move A3 down one row, so things look like this:

    __A _______B_______C
    1 Alberts Alberts =IF(NOT(A1=B1),"different","")
    2 Alberts Alberts =IF(NOT(A2=B2),"different","")
    3 ______ Alberts =IF(NOT(A3=B3),"different","")
    4 Brooks Brooks =IF(NOT(A4=B4),"different","")
    5 Brooks etc...........

    Please note that I'm doing this because there is other information in each row, not because my OCD is out of control. Also, in my real formula I'm handling cells that are blank. Anyway, my problem is that I don't get the above. Rather, Excel adjust the values in my formula so I get:

    __A _______B_______C
    1 Alberts Alberts =IF(NOT(A1=B1),"different","")
    2 Alberts Alberts =IF(NOT(A2=B2),"different","")
    3 ______ Alberts =IF(NOT(A4=B3),"different","")
    4 Brooks Brooks =IF(NOT(A5=B4),"different","")
    5 Brooks etc...........

    This is NOT what I want. I want the formula in C3 to continue to reference A3, not A4. As I understand it, absolute references are only for when you want to copy the cell that they are used in, which I'm not doing. Anyway, I tried saying $A$3, but it didn't help.

    How do I stop this behavior?

    Thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,226

    Re: Keeping cell references in formula the same when cells are inserted

    Try:

    =IF(NOT(OFFSET(B1,0,-1)=B1),"different","")

    and copy down.


    Regards
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-14-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Keeping cell references in formula the same when cells are inserted

    Thanks TMShucks! Unfortunately, there are a couple of problems with the OFFSET function. The first is that I'm not sure how many rows of cells I need to insert; it might be more than one. The second, and more deadly problem, is that the cell reference still changes with OFFSET if I insert the cells. Do you have any other suggestions?


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,226

    Re: Keeping cell references in formula the same when cells are inserted

    No other suggestions, no ... but it seems to work OK for me.

    See the attached example.

    Regards
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-14-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Keeping cell references in formula the same when cells are inserted

    Thanks, TMShucks. I've tried OFFSET out and I think it doesn't really do what I'm looking for, although I haven't had enough time to play with it, I admit. I've decided to attack the problem from a different angle altogether using T-SQL to pull the data in a different way. But -- I appreciate the time you took to think about my problem and offer me a solution.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,226

    Re: Keeping cell references in formula the same when cells are inserted

    You're welcome.

    Maybe you should post a representative sample workbook and describe the various scenarios.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1