Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a list with cells referring to others cells
Hi all. Im doing a big paper on heuristics in economics and I am going crazy
over this detail I cant get to work, hope you can help. I am trying to sort a list of numbers, but the cells in which the numbers are listed are really referring to other cells. When I use the sort function, nothing happens. Cant his be done? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a list with cells referring to others cells
Are you including the souce cells in your sort?
"Friis(DK)" wrote: Hi all. Im doing a big paper on heuristics in economics and I am going crazy over this detail I cant get to work, hope you can help. I am trying to sort a list of numbers, but the cells in which the numbers are listed are really referring to other cells. When I use the sort function, nothing happens. Cant his be done? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a list with cells referring to others cells
Hi bigwheel
I dont wont to sort the sorce cells, only the actual numbers in the new colum. Exampel: Original numbers New colum refering to theoriginal numbers (this i would like to sort) 2 =A2 (2) 965 =A3 (965) 852 =A4 (852) 58 =A5 (58) 51 =A6 (51) 8 =A7 (8) 954 =A8 (954) I would like the outcome to be: Original numbers New colum refering to theoriginal numbers (this is now sorted in the new colum, and not in the original) 2 =A2 (2) 965 =A7 (8) 852 =A6 (51) 58 =A5 (58) 51 =A4 (852) 8 =A8 (954) 954 =A3 (965) Does this make sense? "bigwheel" skrev: Are you including the souce cells in your sort? "Friis(DK)" wrote: Hi all. Im doing a big paper on heuristics in economics and I am going crazy over this detail I cant get to work, hope you can help. I am trying to sort a list of numbers, but the cells in which the numbers are listed are really referring to other cells. When I use the sort function, nothing happens. Cant his be done? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a list with cells referring to others cells
With A2:A10 containing formulas referencing numeric cells Assuming the formula returns an empty string if the referenced cell is blank. Example: A2: =IF(L2<"",L2,"") This formula will list the values in A2:A10 in ascending order: B2: =IF(ROWS($A$2:A2)<=COUNT($A$2:$A$10),SMALL($A$2:$A $10,ROWS($A$2:A2)),"") Example: Refs Col_A Col_B Row_2: 2 2 Row_3: 965 8 Row_4: 852 51 Row_5: 58 58 Row_6: 51 852 Row_7: 8 954 Row_8: 954 965 Row_9: (blank) (blank) Row_10: (blank) (blank) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Friis(DK)" wrote: Hi bigwheel I dont wont to sort the sorce cells, only the actual numbers in the new colum. Exampel: Original numbers New colum refering to theoriginal numbers (this i would like to sort) 2 =A2 (2) 965 =A3 (965) 852 =A4 (852) 58 =A5 (58) 51 =A6 (51) 8 =A7 (8) 954 =A8 (954) I would like the outcome to be: Original numbers New colum refering to theoriginal numbers (this is now sorted in the new colum, and not in the original) 2 =A2 (2) 965 =A7 (8) 852 =A6 (51) 58 =A5 (58) 51 =A4 (852) 8 =A8 (954) 954 =A3 (965) Does this make sense? "bigwheel" skrev: Are you including the souce cells in your sort? "Friis(DK)" wrote: Hi all. Im doing a big paper on heuristics in economics and I am going crazy over this detail I cant get to work, hope you can help. I am trying to sort a list of numbers, but the cells in which the numbers are listed are really referring to other cells. When I use the sort function, nothing happens. Cant his be done? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a list with cells referring to others cells
Hey, you just saved my night - thanks a million, I love the Internet and
everyone on it :) After a little testing I have run into a problem with the formula, I can't use it in the same colum as the numbers i whish to sort are listed in. Is there a way around this? Btw: Am I right in assuming that there are no standard formula in Excel that can do what this formula does? *************** Regards, Friis(DK) XL2007, WinXP "Ron Coderre" skrev: With A2:A10 containing formulas referencing numeric cells Assuming the formula returns an empty string if the referenced cell is blank. Example: A2: =IF(L2<"",L2,"") This formula will list the values in A2:A10 in ascending order: B2: =IF(ROWS($A$2:A2)<=COUNT($A$2:$A$10),SMALL($A$2:$A $10,ROWS($A$2:A2)),"") Example: Refs Col_A Col_B Row_2: 2 2 Row_3: 965 8 Row_4: 852 51 Row_5: 58 58 Row_6: 51 852 Row_7: 8 954 Row_8: 954 965 Row_9: (blank) (blank) Row_10: (blank) (blank) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Friis(DK)" wrote: Hi bigwheel I dont wont to sort the sorce cells, only the actual numbers in the new colum. Exampel: Original numbers New colum refering to theoriginal numbers (this i would like to sort) 2 =A2 (2) 965 =A3 (965) 852 =A4 (852) 58 =A5 (58) 51 =A6 (51) 8 =A7 (8) 954 =A8 (954) I would like the outcome to be: Original numbers New colum refering to theoriginal numbers (this is now sorted in the new colum, and not in the original) 2 =A2 (2) 965 =A7 (8) 852 =A6 (51) 58 =A5 (58) 51 =A4 (852) 8 =A8 (954) 954 =A3 (965) Does this make sense? "bigwheel" skrev: Are you including the souce cells in your sort? "Friis(DK)" wrote: Hi all. Im doing a big paper on heuristics in economics and I am going crazy over this detail I cant get to work, hope you can help. I am trying to sort a list of numbers, but the cells in which the numbers are listed are really referring to other cells. When I use the sort function, nothing happens. Cant his be done? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a list with cells referring to others cells
I can't
use it in the same colum as the numbers i whish to sort are listed in. I'm trying to figure out what you mean in that statement. Can you give an example? *********** Regards, Ron XL2002, WinXP "Friis(DK)" wrote: Hey, you just saved my night - thanks a million, I love the Internet and everyone on it :) After a little testing I have run into a problem with the formula, I can't use it in the same colum as the numbers i whish to sort are listed in. Is there a way around this? Btw: Am I right in assuming that there are no standard formula in Excel that can do what this formula does? *************** Regards, Friis(DK) XL2007, WinXP "Ron Coderre" skrev: With A2:A10 containing formulas referencing numeric cells Assuming the formula returns an empty string if the referenced cell is blank. Example: A2: =IF(L2<"",L2,"") This formula will list the values in A2:A10 in ascending order: B2: =IF(ROWS($A$2:A2)<=COUNT($A$2:$A$10),SMALL($A$2:$A $10,ROWS($A$2:A2)),"") Example: Refs Col_A Col_B Row_2: 2 2 Row_3: 965 8 Row_4: 852 51 Row_5: 58 58 Row_6: 51 852 Row_7: 8 954 Row_8: 954 965 Row_9: (blank) (blank) Row_10: (blank) (blank) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Friis(DK)" wrote: Hi bigwheel I dont wont to sort the sorce cells, only the actual numbers in the new colum. Exampel: Original numbers New colum refering to theoriginal numbers (this i would like to sort) 2 =A2 (2) 965 =A3 (965) 852 =A4 (852) 58 =A5 (58) 51 =A6 (51) 8 =A7 (8) 954 =A8 (954) I would like the outcome to be: Original numbers New colum refering to theoriginal numbers (this is now sorted in the new colum, and not in the original) 2 =A2 (2) 965 =A7 (8) 852 =A6 (51) 58 =A5 (58) 51 =A4 (852) 8 =A8 (954) 954 =A3 (965) Does this make sense? "bigwheel" skrev: Are you including the souce cells in your sort? "Friis(DK)" wrote: Hi all. Im doing a big paper on heuristics in economics and I am going crazy over this detail I cant get to work, hope you can help. I am trying to sort a list of numbers, but the cells in which the numbers are listed are really referring to other cells. When I use the sort function, nothing happens. Cant his be done? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a list with cells referring to others cells
=SMALL($A$1:$A$7,ROWS($1:1))
"Friis(DK)" wrote: Hi all. Im doing a big paper on heuristics in economics and I am going crazy over this detail I cant get to work, hope you can help. I am trying to sort a list of numbers, but the cells in which the numbers are listed are really referring to other cells. When I use the sort function, nothing happens. Cant his be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort a list by comparing two cells. | Excel Discussion (Misc queries) | |||
how do i sort a list from cells by picking a specific letter? | Excel Discussion (Misc queries) | |||
copy & paste formulas while referring to same cells | Excel Discussion (Misc queries) | |||
Referring to Cells in a forumula using a function | Excel Worksheet Functions | |||
referring to other cells with formulas | Excel Worksheet Functions |