Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting formulas with cells
I'm having trouble with what seems like an easy problem. How come when I
sort my data, the forumlas I have in it don't stay relative? For example, I have Seattle Washington 14 Olympia Washington 27 Dallas Texas 8 Austin Texas 11 I've got the Washington numbers adding and the Texas numbers adding at the bottom of my page (C1+C2). But when I sort to alphabetize the city, the formula is still adding C1+C2, not Seattle + Olympia. Austin Texas 11 Dallas Texas 8 Olympia Washington 27 Seattle Washington 14 How do I keep the formula linked to the contents, and not the cell location? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting formulas with cells
hi
if you are using a simple formula like =sum(c1+c2) then you will get that. the above formula evaluated cells not data in the cells. so you will need to use another type formula' =SUMIF(B1:B4,"Washington",C1:C4) or sumif(range to evaluate,criteria, range to sum) regards FSt1 "Jill33" wrote: I'm having trouble with what seems like an easy problem. How come when I sort my data, the forumlas I have in it don't stay relative? For example, I have Seattle Washington 14 Olympia Washington 27 Dallas Texas 8 Austin Texas 11 I've got the Washington numbers adding and the Texas numbers adding at the bottom of my page (C1+C2). But when I sort to alphabetize the city, the formula is still adding C1+C2, not Seattle + Olympia. Austin Texas 11 Dallas Texas 8 Olympia Washington 27 Seattle Washington 14 How do I keep the formula linked to the contents, and not the cell location? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting formulas with cells
Are you sorting just one column, but not the entire data range? When you do
sorts in Excel, make sure to select the ENTIRE data range and then sort according to your specific criteria. If you don't do this, one column will be sorted, but all other columns will remain as they were before you did the sort (I am fairly certain that this is not what you want to do). Access will handle all data ranges automatically, but Excel will not. Regards, Ryan--- -- RyGuy "Jill33" wrote: I'm having trouble with what seems like an easy problem. How come when I sort my data, the forumlas I have in it don't stay relative? For example, I have Seattle Washington 14 Olympia Washington 27 Dallas Texas 8 Austin Texas 11 I've got the Washington numbers adding and the Texas numbers adding at the bottom of my page (C1+C2). But when I sort to alphabetize the city, the formula is still adding C1+C2, not Seattle + Olympia. Austin Texas 11 Dallas Texas 8 Olympia Washington 27 Seattle Washington 14 How do I keep the formula linked to the contents, and not the cell location? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting formulas with cells
Great--thank you! That worked!
"FSt1" wrote: hi if you are using a simple formula like =sum(c1+c2) then you will get that. the above formula evaluated cells not data in the cells. so you will need to use another type formula' =SUMIF(B1:B4,"Washington",C1:C4) or sumif(range to evaluate,criteria, range to sum) regards FSt1 "Jill33" wrote: I'm having trouble with what seems like an easy problem. How come when I sort my data, the forumlas I have in it don't stay relative? For example, I have Seattle Washington 14 Olympia Washington 27 Dallas Texas 8 Austin Texas 11 I've got the Washington numbers adding and the Texas numbers adding at the bottom of my page (C1+C2). But when I sort to alphabetize the city, the formula is still adding C1+C2, not Seattle + Olympia. Austin Texas 11 Dallas Texas 8 Olympia Washington 27 Seattle Washington 14 How do I keep the formula linked to the contents, and not the cell location? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting formulas with cells
Yeah, I was sorting the entire data range. The suggestion posted above
worked for me. It seems like there should be an easier way, but I guess not. Thanks. "ryguy7272" wrote: Are you sorting just one column, but not the entire data range? When you do sorts in Excel, make sure to select the ENTIRE data range and then sort according to your specific criteria. If you don't do this, one column will be sorted, but all other columns will remain as they were before you did the sort (I am fairly certain that this is not what you want to do). Access will handle all data ranges automatically, but Excel will not. Regards, Ryan--- -- RyGuy "Jill33" wrote: I'm having trouble with what seems like an easy problem. How come when I sort my data, the forumlas I have in it don't stay relative? For example, I have Seattle Washington 14 Olympia Washington 27 Dallas Texas 8 Austin Texas 11 I've got the Washington numbers adding and the Texas numbers adding at the bottom of my page (C1+C2). But when I sort to alphabetize the city, the formula is still adding C1+C2, not Seattle + Olympia. Austin Texas 11 Dallas Texas 8 Olympia Washington 27 Seattle Washington 14 How do I keep the formula linked to the contents, and not the cell location? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting cells with formulas | Excel Worksheet Functions | |||
Maintaining formulas when sorting | New Users to Excel | |||
Sorting of References for Formulas | Excel Worksheet Functions | |||
Problems with formulas and sorting | Excel Worksheet Functions | |||
Sorting with formulas | Excel Discussion (Misc queries) |