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:

Transaction Name	Amount	Sum	Amount	Transaction Name
Transaction 1	6	0	6	Transaction 1
Transaction 2	7	4	3	Transaction 3
Transaction 3	3	2	1	Transaction 4
Transaction 4	1	-3	4	Transaction 5
Transaction 5	4	-4	8	Transaction 6

<tbody>
</tbody>

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

Transaction Name	Amount		Amount	Transaction Name
Transaction 1	6	0	6	Transaction 1
Transaction 2	7	4
Transaction 3	3	2	3	Transaction 3
Transaction 4	1	-3	1	Transaction 4
Transaction 5	4	-4	4	Transaction 5

<tbody>
</tbody>

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:

Transaction Name	Amount	Sum	Amount	Transaction Name
Transaction 1	6	0	6	Transaction 1
Transaction 2	7	7
Transaction 3	3	0	3	Transaction 3
Transaction 4	1	0	1	Transaction 4
Transaction 5	4	0	4	Transaction 5

<tbody>
</tbody>

 

[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.