Results 1 to 5 of 5

Thread: Keep cell references in formulas from changing

  1. #1

    Keep cell references in formulas from changing



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Maybe try this

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

  3. #3
    An alternative

    =IF(NOT(INDEX(A:A,ROW())=B1),"different","")

  4. #4
    It's the way you are "moving A3 down one row" that is messing with the formula. It sounds like you are highlighting the entire A column (or the entire column below and including A3) or and doing a cut / paste.

    This will cause the formula to change as you have shown -- when you cut then paste a cell which is referenced in a formula, excel doesn't break the reference (the Value in A3 has moved but excel assumes it's still important to the formula which used it so it tells that formula the value has move to A4). This is a handy feature in other instances.

    Just do one of the following:

    1-Instead of cutting / pasting the column - copy / paste it and then "delete" the value in A3 (by selecting the cell and pressing "delete" not by right clicking and selecting "delete" -- you want to do the equivalent of right-click ->clear contents).

    2-OR just do the cut / paste (or whatever led you to this delimma in the first place) and re-author the formula in the C column -- basically highlight a cell w/ the formula above the "move" (like c1) then highlight/copy formulas to the C column again. (which w/ hotkeys would be, select cell C1 -> press cntrl+c ->press shift+cntrl+ down arrow ->press cntrl+v)

  5. #5
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Why not add a File?
    Providing a file for your particular application will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (it can be dummy data but must be of the same type) and your structure it is far easier for us to give you a tailored, workable answer to your query
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Posting Permissions

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