Keep cell references in formulas from changing

M

MSPeterson

New member
Joined
Sep 14, 2011
Messages
1
Reaction score
0
Points
0
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. :rolleyes: 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!
 
Maybe try this

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

=IF(NOT(INDEX(A:A,ROW())=B1),"different","")
 
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)
 
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 :)
 
You must log in or register to reply here.
Back
Top