# Keeping cell references in formula the same when cells are inserted

1. ## Keeping cell references in formula the same when cells are inserted

Hi - I have a problem that I thought would be solved by using absolute references, but it's not. I have a spreadsheet that looks like this:

__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 Brooks Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")

As you would expect, C3 displays "different", all other cells in column C are blank. So far so good. However, what I want to do, now that I've identified the "different" row, is to move A3 down one row, so things look like this:

__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
5 Brooks etc...........

Please note that I'm doing this because there is other information in each row, not because my OCD is out of control. Also, in my real formula I'm handling cells that are blank. Anyway, my problem is that I don't get the above. Rather, Excel adjust the values in my formula so I get:

__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A4=B3),"different","")
4 Brooks Brooks =IF(NOT(A5=B4),"different","")
5 Brooks etc...........

This is NOT what I want. I want the formula in C3 to continue to reference A3, not A4. As I understand it, absolute references are only for when you want to copy the cell that they are used in, which I'm not doing. Anyway, I tried saying \$A\$3, but it didn't help.

How do I stop this behavior?

Thanks!

2. ## Re: Keeping cell references in formula the same when cells are inserted

Try:

=IF(NOT(OFFSET(B1,0,-1)=B1),"different","")

and copy down.

Regards

3. ## Re: Keeping cell references in formula the same when cells are inserted

Thanks TMShucks! Unfortunately, there are a couple of problems with the OFFSET function. The first is that I'm not sure how many rows of cells I need to insert; it might be more than one. The second, and more deadly problem, is that the cell reference still changes with OFFSET if I insert the cells. Do you have any other suggestions?

4. ## Re: Keeping cell references in formula the same when cells are inserted

No other suggestions, no ... but it seems to work OK for me.

See the attached example.

Regards

5. ## Re: Keeping cell references in formula the same when cells are inserted

Thanks, TMShucks. I've tried OFFSET out and I think it doesn't really do what I'm looking for, although I haven't had enough time to play with it, I admit. I've decided to attack the problem from a different angle altogether using T-SQL to pull the data in a different way. But -- I appreciate the time you took to think about my problem and offer me a solution.

6. ## Re: Keeping cell references in formula the same when cells are inserted

You're welcome.

Maybe you should post a representative sample workbook and describe the various scenarios.

Regards

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1