How to lock cell reference when using EXACT fx

[Rainbows8]

Hi all,

I have a spreadsheet that evaluates cells against eachother and if they don't match I either delete the whole row, or delete up. When I do this, I have to redo the formula as I get an error.

So I start with: =EXACT(A1,G1).... and once I delete a row or delete "up", I get =EXACT(#REF!,$G3).

I have tried =EXACT(INDIRECT(A1,G1)), =EXACT(INDIRECT($A1,$G1)), and also =EXACT($A1,$G1).

None of these work.
Please can someone help me?

Thank you

 

[Rainbows8]

This regular formula always compares A1 to B1...even if either or both of those cells is deleted up.

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

Copy that formula down as far as you need.

Thank you so much! I double checked it, and of course it does work.... please excuse my initial error since when I check it at first it didn't work, but that was probably something I was doing and perhaps cos it's a Monday!

 

[Ron Coderre]

Glad you got something you could use!

 

[shg]

Or

=EXACT(A:A, B:B)