I'm working on correcting these large two excel sheets. I need the sequence of entries to match up and I need to make sure they both have all of the same entries.

To check for this I created two extra supporter sheets to show me the work that needs to be done. One that shows me if the order of entries match up and one to show me which ones are not entered in the other sheet.

I created the first one with a simple side-by-side column comparison. Column A references the entries in sheet1 and Column B references sheet2. And I have Column C simply doing a match comparison between the two. Showing True or False.

The second supporter sheet follows the side-by-side comparison as the first and finds discrepancies via conditional formatting, coloring in a cell if it does not match any of the other entries in the opposite column (this was done with two rules).

The issue is that when I update either sheet1 or sheet2 by inserting a new row on one of the sheets my references in my supporter sheets no longer align. Some show the #Ref! error though some simply show and incremented reference. (i.e. $A7 when I still want $A6).

My formula for Column A and Column B are simply ="sheet1"$A6 and ="sheet2"$B4.

It might be noteworthy that sheet1 and sheet2 are actually different files.

Is there any way to lock in my references? So they always reference that particular cell? Even when I insert a new one in the worksheets they reference?

  • 1
    Use absolute references ="sheet1"$A$6 and ="sheet2"$B$4 – yass Apr 24 '17 at 18:50
  • If you're saying that ="sheet1"$A6 becomes say ="Sheet1"$A7 when inserting a new row, you want to use Indirect(). i.e. =Indirect("'Sheet1'!$A$7") I believe. – BruceWayne Apr 24 '17 at 19:52
  • @yass Absolute References are not what I'm going for. Because I do want the next row to increment. Is there an easy way to use $A$6 so that it increments with every row? – CGTheLegend Apr 24 '17 at 20:15
  • @BruceWayne I don't think that INDIRECT will help, because I am referencing the direct cell I want to populate the cell. Not another reference. – CGTheLegend Apr 24 '17 at 20:18
  • Sorry if I'm not being clear enough. I updated the post to try and clear things up. – CGTheLegend Apr 24 '17 at 20:24

Use this Formula:


Even if you insert Row it will always reference the same cell as where the formula is because of RC reference and you can drag it down and in the same row

  • I'm sorry I don't follow. What does &"R"&ROW()&"C"&COLUMN() represent? Just a general cell? And why are you just checking if it equates to ','? – CGTheLegend Apr 25 '17 at 16:51
  • I just want my cell to populate the same cell location when I insert a new row in the sheet it references. Maybe I'm missing something? – CGTheLegend Apr 25 '17 at 16:52
  • &"R"&ROW()&"C"&COLUMN() is the reference of the same cell in another sheet using R1C1 reference (number of row R and number of column C) instead of A6 try it – Sam Apr 26 '17 at 6:35

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.