0

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?

Improve this question
6
  • 1
    Use absolute references ="sheet1"$A$6 and ="sheet2"$B$4
    – yass
    Commented Apr 24, 2017 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
    Commented Apr 24, 2017 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
    Commented Apr 24, 2017 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
    Commented Apr 24, 2017 at 20:18
  • Sorry if I'm not being clear enough. I updated the post to try and clear things up.
    – CGTheLegend
    Commented Apr 24, 2017 at 20:24

1 Answer 1

Reset to default
1

Use this Formula: 

=IF(INDIRECT("Sheet1!"&"R"&ROW()&"C"&COLUMN(),FALSE)="","",INDIRECT("Sheet1!"&"R"&ROW()&"C"&COLUMN(),FALSE))

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

Improve this answer
3
  • 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
    Commented Apr 25, 2017 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
    Commented Apr 25, 2017 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
    Commented Apr 26, 2017 at 6:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .