Sorting and formula reference

[Stefanie]

I recently upgraded to Excel 2007 and now I'm noticing this problem.

Col A Col B Col C
Code: Location: Percent:
55 Colorado =C25
25 Nebraska =C26
35 Kansas =C27
45 Texas =C28

Column C is a formula referencing a cell further down in the spreadsheet.
When I sort the above four lines by either code or location, the formula in
column C changes. Example, the Colorado location formula would now be =C28.

I thought when you sort, the formula should always reference the same cell.
I have not used $ in my formula. I've used this spreadsheet in Excel 2003 and
I don't remember having this problem, but I can't go back and check it since
I have upgraded to 2007. I can fix the problem by adding $ in the formula,
but I'm curious if anyone else has experienced this or has any wisdom?

Thanks, Stefanie

 

[Sheeloo]

Are you sure?

I tested with both Excel 2003 and 2007...
Col C formulas did NOT change and retained the references
=C25
=C26
=C27
=C28

See http://wikisend.com/download/585278/Sorting.xlsx

 

[Stefanie]

When I sort, it messes up the references. Do you know if there is a setting I
need to fix in 2007?

 

[Sheeloo]

I am not aware of any...

Can you share your file?

 

[Stefanie]

Sure, what do I need to do?

I am not aware of any...

Can you share your file?

 

[Sheeloo]

either mail it to me or upload to wikisend.com and paste the link here