Formula using current row

  • Thread starter Thread starter Jeremy Leonard
  • Start date Start date
J

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.
 
Use fix reference by adding $ sign something like this:
IF(exact(A$5,C$5),"ok","modify")
 
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.
 
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
 
Back
Top