Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

Excel Question

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

share|improve this question
    
I don't have time to try it now, but could you use a combination of OFFSET and ROW to match the values in the formula's row, regardless of where cells are added? –  dav Jul 24 '12 at 20:42

1 Answer 1

up vote 2 down vote accepted

If I understand your question, I think this may work:

=OFFSET($B$1,ROW()-1,-1)=OFFSET($B$1,ROW()-1,1)

Where B1 is an arbitrary anchor for your formulas and you always calculate the offset to the cell on either side of your comparison.

share|improve this answer
    
From your original comment I was not sure what to do with ROW, but I actually got it to work with OFFSET alone, like this: =OFFSET(B1,0,-1)=OFFSET(B1,0,1). I'm still curious if there is a way to lock the formulas, but if no one has posted anything to that effect in a few days I'll accept this answer. Thanks! –  techturtle Jul 24 '12 at 21:04
    
Thanks, your formula is actually a bit more elegant than mine. The ROW() should "lock" the formula to the current row, but its really unnecessary as your modification shows. As for "locking" the formulas, what are you trying to accomplish-I'm not sure I understand completely. –  dav Jul 25 '12 at 11:57
    
Basically I would like to be able to continue using =A1=C1 (easier to setup and read) and drag that down, then select the column and say "Lock" so it won't update the formula/cells anymore. I thought there might have been a setting in options I could turn on temporarily, something like "don't update formulas on move", but I haven't found anything that works. –  techturtle Jul 25 '12 at 15:10
    
Ok, I think I understand better. I've never heard of something like that, although it seems like VBA should be capable of it (but I'm not very good with VBA). To improve readability (but short of locking like you've mentioned) you could probably use named ranges. I'll see if I can get something to work... –  dav Jul 25 '12 at 19:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.