Sometimes I visually compare two datasets by pasting them side-by-side in Excel with a formula in the middle like =A1=C1. This will return true or false based on whether the cells are an exact match. When the datasets are missing a record, I can get them to line up again by simply inserting a new cell and shifting the existing ones down. However, when I do this it updates the formulas to shift the referring cell down by the amount of cells I inserted, like this:
This causes problems because I have to redo all the formulas down the middle each time this happens. On a long dataset, this gets very tedious. Is there any way to "lock" the formulas in the comparison columns so it doesn't care if I insert or delete cells, they will always stay pointing to the original cells I set up?
BTW, I know that $A$1 will lock a reference, but that only works if you're moving that cell around, not moving the referenced cell. If you put that in at the top and drag them down, they will all stay $A$1, no matter what row you drag to (plus, it still does the update after inserting a cell in the reference column).
Best Answer
If I understand your question, I think this may work:
Where B1 is an arbitrary anchor for your formulas and you always calculate the offset to the cell on either side of your comparison.