Locking a cell reference to a cell location

[jimp823]

Hi,

I have a spreadsheet where I am comparing two lists of chronological transactions based on their amount. For example column B has an amount for the transaction from the first list and column D has a transaction from the second list. In column C I have the formula '=B1-D1' and if they sum to 0 I know they match.

If they don't sum to zero, that means there's another transaction in one of the lists that isn't in the other list, and I can identify the missing transaction. I then insert cells above the list with the missing transaction so that the next set of transactions align. However, when the list of transactions shifts down, the formula in column C changes the cell target to move with it. Ex. If I shift down the list in column D starting in row 2, the formula changes from '=B2-D2' to '=B2-D3'.

Is there a way to lock the formula in Column C so that is stays the same no matter what I do with the other columns?

Right now, the only way I can continue auditing my transactions is by recopying the formula down column C to the bottom again.

Here's what the table looks like:

[TABLE="width: 771"]
<tbody>[TR]
[TD]Transaction Name[/TD]
[TD]Amount[/TD]
[TD] Sum[/TD]
[TD]Amount[/TD]
[TD]Transaction Name[/TD]
[/TR]
[TR]
[TD]Transaction 1[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Transaction 1[/TD]
[/TR]
[TR]
[TD]Transaction 2[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]Transaction 3[/TD]
[/TR]
[TR]
[TD]Transaction 3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Transaction 4[/TD]
[/TR]
[TR]
[TD]Transaction 4[/TD]
[TD]1[/TD]
[TD]-3[/TD]
[TD]4[/TD]
[TD]Transaction 5[/TD]
[/TR]
[TR]
[TD]Transaction 5[/TD]
[TD]4[/TD]
[TD]-4[/TD]
[TD]8[/TD]
[TD]Transaction 6[/TD]
[/TR]
</tbody>[/TABLE]


Here's what it looks like after I've shifted the cells in columns D and E down to match up:

[TABLE="width: 771"]
<tbody>[TR]
[TD]Transaction Name[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD]Transaction Name[/TD]
[/TR]
[TR]
[TD]Transaction 1[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Transaction 1[/TD]
[/TR]
[TR]
[TD]Transaction 2[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Transaction 3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Transaction 3[/TD]
[/TR]
[TR]
[TD]Transaction 4[/TD]
[TD]1[/TD]
[TD]-3[/TD]
[TD]1[/TD]
[TD]Transaction 4[/TD]
[/TR]
[TR]
[TD]Transaction 5[/TD]
[TD]4[/TD]
[TD]-4[/TD]
[TD]4[/TD]
[TD]Transaction 5[/TD]
[/TR]
</tbody>[/TABLE]


And here's what I need, which right now I can only acheive by recopying the formula from C2 all the way down the column again:

[TABLE="width: 771"]
<tbody>[TR]
[TD]Transaction Name[/TD]
[TD]Amount[/TD]
[TD]Sum[/TD]
[TD]Amount[/TD]
[TD]Transaction Name[/TD]
[/TR]
[TR]
[TD]Transaction 1[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Transaction 1[/TD]
[/TR]
[TR]
[TD]Transaction 2[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Transaction 3[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]Transaction 3[/TD]
[/TR]
[TR]
[TD]Transaction 4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]Transaction 4[/TD]
[/TR]
[TR]
[TD]Transaction 5[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]Transaction 5[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Try

=INDIRECT(ADDRESS(ROW(),2))-INDIRECT(ADDRESS(ROW(),4)) in C2

 

[Peter_SSs]

In C2, copied down
=B2-OFFSET(C2,0,1)

Note though that OFFSET & INDIRECT are volatile functions so can slow your sheet if used a lot.
If you wanted to avoid the volatile function you could instead use
=B2-INDEX(D:D,ROW())

 

[jimp823]

Thanks, Peter. I used '=B2-INDEX(D:D,ROW())' and it worked like a charm!

 

[Peter_SSs]

Thanks, Peter. I used '=B2-INDEX(D:D,ROW())' and it worked like a charm!

You're welcome. Glad it helped.