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:
<tbody>
</tbody>
Here's what it looks like after I've shifted the cells in columns D and E down to match up:
<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:
<tbody>
</tbody>
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>
Last edited: