Formula using current row

[Jeremy Leonard]

I have a formula that I am using to gather some summary information at
the far right of the data I have in my spreadsheet.

An example of this fomula is:
=IF(exact(A5,C5),"ok","modify")

The problem I am running into is that if I insert data or remove data,
the formula will move the row that it is getting the information from.

Is there a way so that on the 5th row it is always looking at A5 and
C5, instead of moving to A6 if I add something in the A column?

Thanks in advance.

 

[Michael]

Use fix reference by adding $ sign something like this:
IF(exact(A$5,C$5),"ok","modify")

 

[Jim Rech]

This always refers to A5 and C5 regardless of moves or inserts.

=IF(EXACT(INDIRECT("A5"),INDIRECT("C5")),"ok","modify")

--
Jim
|I have a formula that I am using to gather some summary information at
| the far right of the data I have in my spreadsheet.
|
| An example of this fomula is:
| =IF(exact(A5,C5),"ok","modify")
|
| The problem I am running into is that if I insert data or remove data,
| the formula will move the row that it is getting the information from.
|
| Is there a way so that on the 5th row it is always looking at A5 and
| C5, instead of moving to A6 if I add something in the A column?
|
| Thanks in advance.

 

[Shane Devenshire]

Hi,

First a comment about EXACT - all this adds to you comparing two cells is a
check for case match.

=IF(EXACT(OFFSET(A1,4,0),OFFSET(A1,4,2)),"OK","modified")

You can refer to any fixed cell instead of A1 and adjust to row and column
arguments accordingly.

IF this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Jeremy Leonard]

Thank you Shane,

This worked perfectly for me.