Sorting and formula reference
 

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

Sorting and formula reference
 

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" wrote:

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

Sorting and formula reference
 

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

"Sheeloo" wrote:

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" wrote:

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

Sorting and formula reference
 

I am not aware of any...

Can you share your file?

"Stefanie" wrote:

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

"Sheeloo" wrote:

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" wrote:

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

Sorting and formula reference
 

Sure, what do I need to do?

"Sheeloo" wrote:

I am not aware of any...

Can you share your file?

"Stefanie" wrote:

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

"Sheeloo" wrote:

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" wrote:

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

Sorting and formula reference
 

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

"Stefanie" wrote:

Sure, what do I need to do?

"Sheeloo" wrote:

I am not aware of any...

Can you share your file?

"Stefanie" wrote:

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

"Sheeloo" wrote:

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" wrote:

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, StefanieEi