| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
| Retaining cell reference in destination, when sorting source file | Excel Worksheet Functions | |||
| Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
| Moving Cell Reference after Sorting | New Users to Excel | |||
| keep cell data in reference after sorting | Excel Discussion (Misc queries) | |||
Linear Mode
