Help locking a formulas cell reference

[garden_gnome]

I am looking for an addition to my formulas so that the cell reference will not change when the corresponding cells have been moved. ie. insert, cut, copy, paste.
Here is an example....

1 1 TRUE =A1=B1
2 2 TRUE =A2=B2
3 3 TRUE =A3=B3
4 4 TRUE =A4=B4
4 5 FALSE =A5=B5
5 6 FALSE =A6=B6
6 7 FALSE =A7=B7

The current TRUE/FALSE formulas are =a1=b1. You can see that if I want my data to line up, I need to insert a cell above b5. (Col A data can not be altered due to my needs) If I simply insert that cell, the formulas for A5 changes due to that cell reference changing.

1 1 TRUE =A1=B1
2 2 TRUE =A2=B2
3 3 TRUE =A3=B3
4 4 TRUE =A4=B4
4 _ FALSE =A5=B6
5 5 FALSE =A6=B7
6 6 FALSE =A7=B8

I can not just us =$A$1=$B$1 and so on, as I would have to hand write this for several thousand lines...
I am not sure if I am missing something simple, or if it is going to be complex.
Any help would be appreciated!

Thanks!

[vlady]

Hello

I can not just us =$A$1=$B$1 and so on, as I would have to hand write this for several thousand lines.

You don't have to write it all along one by one.

use the fill handle tool. drag it down

[Haseeb Avarakkan]

Try this formula, copy down.

=INDEX(A:A,ROW())=INDEX(B:B,ROW())

[garden_gnome]

Try this formula, copy down.

=INDEX(A:A,ROW())=INDEX(B:B,ROW())

Need more Help...
Your INDEX formula does solve my issue, however if you seen my post about hiding the TRUE/FALSE when the cells are blank, then the formula does not fully work.

=IF(AND(A1="",B1=""),"",INDEX(A:A,ROW())=INDEX(B:B,ROW()))

Now the INDEX part will stay, but if I happen to change any of the corresponding cells change, my AND formula starts to change....
Not sure if I can use the same INDEX type formula for that part of my argument.

I am hoping you can solve this for me as well... It would conquer a HUGE excel sheet I have.
Thanks!

[garden_gnome]

Need more Help...
Your INDEX formula does solve my issue, however if you seen my post about hiding the TRUE/FALSE when the cells are blank, then the formula does not fully work.

=IF(AND(A1="",B1=""),"",INDEX(A:A,ROW())=INDEX(B:B,ROW()))

Now the INDEX part will stay, but if I happen to change any of the corresponding cells change, my AND formula starts to change....
Not sure if I can use the same INDEX type formula for that part of my argument.

I am hoping you can solve this for me as well... It would conquer a HUGE excel sheet I have.
Thanks!

After some playing around, this is what I came up with and it seems to work....

=IF(AND(INDEX(A:A,ROW())="",INDEX(B:B,ROW())=""),"",INDEX(A:A,ROW())=INDEX(B:B,ROW()))

It's a little long and complex for me, but I am only a beginner.
Thanks for leading me down the right path!

[garden_gnome]

Haseeb A:
Thank you SO VERY MUCH!~ That seems to work perfectly for the moment. I did modify so that it does not show the TRUE/FALSE if both A1 & B1 are blank...

=IF(AND(A1="",B1=""),"",INDEX(A:A,ROW())=INDEX(B:B,ROW()))

I am sure I will be posting more on this site as I have a large amount of Excel work to do.