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

 

[mole999]

=EXACT($A1,$G1)

and deleting a complete row works as it readjusts.. if you delete just parts then you can destroy the reference i.e delete A1 0r G1 and we have the error

 

[Ron Coderre]

Assuming your formula is contained in Row_2, try this structure formula

D2: =EXACT(INDEX(A:A,ROW()-1),INDEX(G:G,ROW()-1)

Now, even if the referenced cells/rows are deleted...the formula will automatically reference the same relative cells.

Adjust the "-1" based on where your formula is located relative to the cells to be referenced.

Is that something you can work with?

 

[Rainbows8]

Assuming your formula is contained in Row_2, try this structure formula

D2: =EXACT(INDEX(A:A,ROW()-1),INDEX(G:G,ROW()-1)

Now, even if the referenced cells/rows are deleted...the formula will automatically reference the same relative cells.

Adjust the "-1" based on where your formula is located relative to the cells to be referenced.

Is that something you can work with?

@Ron Coderre, no because my goal is to compare two lengthy lists to ensure each item matches, when they don't, I remove that cell: e.g.

Cell#..A...B... C
1..... 1...1...=EXACT(A1,B1)
2..... 2...3...=EXACT(A2,B2)
3..... 3...3...=EXACT(A3,B3)

now, let's say I want to delete "up" cell A2 to correct an issue... the EXACT fx gets messed up and doesn't know where to reference. Is there a way to make the formula not relative but static, so that A2 will always reference A2, even if I delete that cell "upwards"....

 

[Ron Coderre]

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.

 

[tusharm]

Not sure I understand what you are doing, but consider =EXACT(INDIRECT("A1"),INDIRECT("G1"))

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

 

[mole999]

@Ron Coderre, no because my goal is to compare two lengthy lists to ensure each item matches, when they don't, I remove that cell: e.g.

.

That explains why the ref is wrong as you are deleting cells with that formula

Rons =EXACT(INDEX(A:A,ROW()),INDEX(G:G,ROW())) does what you need

 

[Rainbows8]

=EXACT($A1,$G1)

and deleting a complete row works as it readjusts.. if you delete just parts then you can destroy the reference i.e delete A1 0r G1 and we have the error

@mole999 - makes sense, is there a way around the error? (see my answer to ron)

 

[mole999]

=EXACT(INDEX(A:A,ROW()),INDEX(G:G,ROW()))

Rons Answer

 

[Rainbows8]

=EXACT(INDEX(A:A,ROW()),INDEX(G:G,ROW()))

Rons Answer

Thank you; I double checked it, and it works. @mole999