[ms office - 2003] Excel - Changing cell reference

[park_bench]

Hello,

 

I have a maths teacher who tells me:

 

He has a spreadsheet with formulas. Within one of the formulas there is a cell reference to another cell in the same row.

 

After doing some filtering on the spreadsheet he realises that the cell reference in the formula has changed to another cell in a different row, thus returning the wrong result.

 

I can't replicate this problem. Has anybody come across this before and do you know what causes it?

 

Thanks a lot,

 

Ben

[Theblacksheep]

I've only had it with user errors.

 

Locking the cells that dont need to be changed fixed it in my cases, the user couldnt modify them anymore (even tho they said they didnt in the first place).

[leco]

I've only had it with user errors.

 

Locking the cells that dont need to be changed fixed it in my cases, the user couldnt modify them anymore (even tho they said they didnt in the first place).

 

Similar experiences here and agree locking cells worked. Users often "forget" that they have moved, copied or deleted something leading to errors that "cannot be explained".

[park_bench]

OK - replicated the problem.

 

It doesn't happen when you filter the data but when you sort data. The cell references with the formulas are corrupted.

 

There are threads on the web relating to this so I'll have a trawl through.

 

Thanks again.

 

Ben

Edited May 1, 2009 by park_bench

[leco]

OK - replicated the problem.

It doesn't happen when you filter the data but when you sort data. The cell references with the formulas are corrupted.

Ben

 

The references will get changed when sorting if they are relative and not absolute.

[park_bench]

The problem occurs when you have formulas referencing cells on another worksheet and even occurs if you have absolute references.

 

The way I worked around it was to pull the result of the referenced cell from the other worksheet onto the same worksheet. Then change the the offending formula to reference the result on the same worksheet.

 

Ben