| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting with a list
I am trying to sort time values using a list. The time values in the cells
are calculated by a formula and not manually entered in. When I try and sort them assending using a list it doesn't even come close to sorting correctly. How do you sort using a list when the values are calculated by formulas? Thanks. Any help would be greatly appreciated. -- Brian |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting with a list
My bet is that you have a mixture of real times and times entered as text--that
only look like time. If you format that column as General, do your cells display numbers between 0 and 1? (or maybe your cells contain dates and times and your cells are formatted to show only the time???) brianwakin wrote: I am trying to sort time values using a list. The time values in the cells are calculated by a formula and not manually entered in. When I try and sort them assending using a list it doesn't even come close to sorting correctly. How do you sort using a list when the values are calculated by formulas? Thanks. Any help would be greatly appreciated. -- Brian -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting with a list
If I format the column as general I do have numbers between 0 and 1. My
cells are formatted to show only time (h:mm am/pm). I don't understand what is going on here. If I hand type the same information in it sorts fine, if the information is generated with a formula it won't sort right. Any suggestions or more thoughts on this would be greatly appreciated. Thanks for your reply. -- Brian "Dave Peterson" wrote: My bet is that you have a mixture of real times and times entered as text--that only look like time. If you format that column as General, do your cells display numbers between 0 and 1? (or maybe your cells contain dates and times and your cells are formatted to show only the time???) brianwakin wrote: I am trying to sort time values using a list. The time values in the cells are calculated by a formula and not manually entered in. When I try and sort them assending using a list it doesn't even come close to sorting correctly. How do you sort using a list when the values are calculated by formulas? Thanks. Any help would be greatly appreciated. -- Brian -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting with a list
Ok.
How about this? You have formulas that look like: =sheet2!a2 =sheet2!a17 =sheet2!a29 =sheet2!a3 These kind of links don't work nicely with sorts. If that's not it, you may want to post your a typical formula. brianwakin wrote: If I format the column as general I do have numbers between 0 and 1. My cells are formatted to show only time (h:mm am/pm). I don't understand what is going on here. If I hand type the same information in it sorts fine, if the information is generated with a formula it won't sort right. Any suggestions or more thoughts on this would be greatly appreciated. Thanks for your reply. -- Brian "Dave Peterson" wrote: My bet is that you have a mixture of real times and times entered as text--that only look like time. If you format that column as General, do your cells display numbers between 0 and 1? (or maybe your cells contain dates and times and your cells are formatted to show only the time???) brianwakin wrote: I am trying to sort time values using a list. The time values in the cells are calculated by a formula and not manually entered in. When I try and sort them assending using a list it doesn't even come close to sorting correctly. How do you sort using a list when the values are calculated by formulas? Thanks. Any help would be greatly appreciated. -- Brian -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting with a list
Here's a typical formula: =IF(AND($A7<"",$B7<"",$C7<""),$B7,"")
Thanks again. -- Brian "Dave Peterson" wrote: Ok. How about this? You have formulas that look like: =sheet2!a2 =sheet2!a17 =sheet2!a29 =sheet2!a3 These kind of links don't work nicely with sorts. If that's not it, you may want to post your a typical formula. brianwakin wrote: If I format the column as general I do have numbers between 0 and 1. My cells are formatted to show only time (h:mm am/pm). I don't understand what is going on here. If I hand type the same information in it sorts fine, if the information is generated with a formula it won't sort right. Any suggestions or more thoughts on this would be greatly appreciated. Thanks for your reply. -- Brian "Dave Peterson" wrote: My bet is that you have a mixture of real times and times entered as text--that only look like time. If you format that column as General, do your cells display numbers between 0 and 1? (or maybe your cells contain dates and times and your cells are formatted to show only the time???) brianwakin wrote: I am trying to sort time values using a list. The time values in the cells are calculated by a formula and not manually entered in. When I try and sort them assending using a list it doesn't even come close to sorting correctly. How do you sort using a list when the values are calculated by formulas? Thanks. Any help would be greatly appreciated. -- Brian -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting with a list
And this formula is in Row 7??
If yes, I would guess that it works. If it's not on row 7, then the sort will mess up what the formula points to--and when the formulas get reevaluated, it'll look weird. brianwakin wrote: Here's a typical formula: =IF(AND($A7<"",$B7<"",$C7<""),$B7,"") Thanks again. -- Brian "Dave Peterson" wrote: Ok. How about this? You have formulas that look like: =sheet2!a2 =sheet2!a17 =sheet2!a29 =sheet2!a3 These kind of links don't work nicely with sorts. If that's not it, you may want to post your a typical formula. brianwakin wrote: If I format the column as general I do have numbers between 0 and 1. My cells are formatted to show only time (h:mm am/pm). I don't understand what is going on here. If I hand type the same information in it sorts fine, if the information is generated with a formula it won't sort right. Any suggestions or more thoughts on this would be greatly appreciated. Thanks for your reply. -- Brian "Dave Peterson" wrote: My bet is that you have a mixture of real times and times entered as text--that only look like time. If you format that column as General, do your cells display numbers between 0 and 1? (or maybe your cells contain dates and times and your cells are formatted to show only the time???) brianwakin wrote: I am trying to sort time values using a list. The time values in the cells are calculated by a formula and not manually entered in. When I try and sort them assending using a list it doesn't even come close to sorting correctly. How do you sort using a list when the values are calculated by formulas? Thanks. Any help would be greatly appreciated. -- Brian -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting with a list
No, the formula is not in row 7. The formula is taking times from another
area of the spreadsheet only if the conditions in the formula are met. Is there any way I can make it sort? Thanks again. -- Brian "Dave Peterson" wrote: And this formula is in Row 7?? If yes, I would guess that it works. If it's not on row 7, then the sort will mess up what the formula points to--and when the formulas get reevaluated, it'll look weird. brianwakin wrote: Here's a typical formula: =IF(AND($A7<"",$B7<"",$C7<""),$B7,"") Thanks again. -- Brian "Dave Peterson" wrote: Ok. How about this? You have formulas that look like: =sheet2!a2 =sheet2!a17 =sheet2!a29 =sheet2!a3 These kind of links don't work nicely with sorts. If that's not it, you may want to post your a typical formula. brianwakin wrote: If I format the column as general I do have numbers between 0 and 1. My cells are formatted to show only time (h:mm am/pm). I don't understand what is going on here. If I hand type the same information in it sorts fine, if the information is generated with a formula it won't sort right. Any suggestions or more thoughts on this would be greatly appreciated. Thanks for your reply. -- Brian "Dave Peterson" wrote: My bet is that you have a mixture of real times and times entered as text--that only look like time. If you format that column as General, do your cells display numbers between 0 and 1? (or maybe your cells contain dates and times and your cells are formatted to show only the time???) brianwakin wrote: I am trying to sort time values using a list. The time values in the cells are calculated by a formula and not manually entered in. When I try and sort them assending using a list it doesn't even come close to sorting correctly. How do you sort using a list when the values are calculated by formulas? Thanks. Any help would be greatly appreciated. -- Brian -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting with a list
Convert to values and sort that??
I think if copy that column and convert that second column to values, and sort by that one, then your formulas won't point to the cells you want--but you could try it. brianwakin wrote: No, the formula is not in row 7. The formula is taking times from another area of the spreadsheet only if the conditions in the formula are met. Is there any way I can make it sort? Thanks again. -- Brian "Dave Peterson" wrote: And this formula is in Row 7?? If yes, I would guess that it works. If it's not on row 7, then the sort will mess up what the formula points to--and when the formulas get reevaluated, it'll look weird. brianwakin wrote: Here's a typical formula: =IF(AND($A7<"",$B7<"",$C7<""),$B7,"") Thanks again. -- Brian "Dave Peterson" wrote: Ok. How about this? You have formulas that look like: =sheet2!a2 =sheet2!a17 =sheet2!a29 =sheet2!a3 These kind of links don't work nicely with sorts. If that's not it, you may want to post your a typical formula. brianwakin wrote: If I format the column as general I do have numbers between 0 and 1. My cells are formatted to show only time (h:mm am/pm). I don't understand what is going on here. If I hand type the same information in it sorts fine, if the information is generated with a formula it won't sort right. Any suggestions or more thoughts on this would be greatly appreciated. Thanks for your reply. -- Brian "Dave Peterson" wrote: My bet is that you have a mixture of real times and times entered as text--that only look like time. If you format that column as General, do your cells display numbers between 0 and 1? (or maybe your cells contain dates and times and your cells are formatted to show only the time???) brianwakin wrote: I am trying to sort time values using a list. The time values in the cells are calculated by a formula and not manually entered in. When I try and sort them assending using a list it doesn't even come close to sorting correctly. How do you sort using a list when the values are calculated by formulas? Thanks. Any help would be greatly appreciated. -- Brian -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorting a list | Excel Worksheet Functions | |||
| List sorting | Excel Discussion (Misc queries) | |||
| Sorting a List | Excel Discussion (Misc queries) | |||
| sorting a list | Excel Discussion (Misc queries) | |||
| Sorting a list | New Users to Excel | |||
Linear Mode
