Absolute values in a formula?

[MSPeterson]

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!

 

[AlphaFrog]

Put this in C1 and drag down column C

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

 

[Scott Huish]

Hi, welcome to the board!

First of all, there is no need for NOT, the not equal operator is <>

However you could just reverse your TRUE/FALSE and use =

=IF(A1=B1,"","different")

However a formula can not move or delete values out of a cell, you will need VBA for this.

 

[MSPeterson]

Hi AlphaFrog - thanks for your quick reply. Alas, INDEX is not what I'm looking for. If I use it, the cell references still change if I insert cells in my spreadsheet. If you have any other suggestions, please send them along!

 

[AlphaFrog]

Then I don't understand what you are asking for.

Besides the INDEX function, there are a couple of other techniques you can use.

The INDIRECT function converts a text string into a cell reference. The text will not change if you insert rows or columns.
=INDIRECT("A4")
...will always reference cell A4

You could also use the offset function
=OFFSET($A$1,3,0)
...will always reference cell A4 as long as you don't insert a cell above or to the left of cell A1

 

[MSPeterson]

Thanks, everyone. I've tried your suggestionsout and I think they don't really do what I'm looking for, although I haven't had enough time to play with them, 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.