Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can't Change and Re-Sort
I have a spreadsheet that has many columns, some of which I am trying to sort
by. One Column is called Closed Date, and is in Date Format. Another column is called Referred Date and is in Date Format. And another is called Status and is in General Format. I am sorting the spreadsheet first by Closed Date, then by Referred Date, then by Status. It sorts fine. nIf I correct a date and re-sort, it re-sorts fine. BUT, if I correct a date and a status, it does not re-sort correctly...the changed records is at the bottom of the spreadsheet out of the sort sequence. I just can't figure out what is happening and why it won't re-sort. HELP!!! |
#2
|
|||
|
|||
Look at the closed date (the first sort key) of the row that does not sort
correctly and is now at the bottom of the table. Is it a valid date? Select that cell and do Format - Cells - Select the Number tab. What is the highlighted format? If the format is not Date but it looks like a valid date, select a blank cell and do Edit - Copy. Then again select the bad closed date cell and do Edit - Paste Special, select Add and click OK. Now sort the table and see if this corrects it. Post back if this doesn't solve it for you. Provide the detail values of the 3 entries in the row that doesn't sort correctly. HTH Otto "Sylvia" wrote in message ... I have a spreadsheet that has many columns, some of which I am trying to sort by. One Column is called Closed Date, and is in Date Format. Another column is called Referred Date and is in Date Format. And another is called Status and is in General Format. I am sorting the spreadsheet first by Closed Date, then by Referred Date, then by Status. It sorts fine. nIf I correct a date and re-sort, it re-sorts fine. BUT, if I correct a date and a status, it does not re-sort correctly...the changed records is at the bottom of the spreadsheet out of the sort sequence. I just can't figure out what is happening and why it won't re-sort. HELP!!! |
#3
|
|||
|
|||
Just a guess, but it sounds like some of your dates are Text and some are really
Dates. I'd format the date cells (all the columns) using the long date: mmmm dd, yyyy And see if there were any cells that didn't change correctly. If you find those, then fix them (reentering the date???), then you can format the way you like and try resorting. Since you're doing a lot of work, you may want to do this against a copy of your workbook/worksheet--just in case. Or make sure you close without saving if really bad things happen. Sylvia wrote: I have a spreadsheet that has many columns, some of which I am trying to sort by. One Column is called Closed Date, and is in Date Format. Another column is called Referred Date and is in Date Format. And another is called Status and is in General Format. I am sorting the spreadsheet first by Closed Date, then by Referred Date, then by Status. It sorts fine. nIf I correct a date and re-sort, it re-sorts fine. BUT, if I correct a date and a status, it does not re-sort correctly...the changed records is at the bottom of the spreadsheet out of the sort sequence. I just can't figure out what is happening and why it won't re-sort. HELP!!! -- Dave Peterson |
#4
|
|||
|
|||
The dates in the Complete and Referred Columns are Date Format. They sort
and and I can change a date and resort without any problem. The third Column is Status, which is a text column. The problem is when I changed the status, then try to resort, the changed line appears at the bottom, and out of sort. Just to further complicate things. This is data from an Access Query that was imported into Excel. For some reason, if the export from Access to Excel was thru "Analyze It with Excel" or exported thru a Macro, the sort problem occured. If I exported it using Export from the File Drop Down, it works fine. Any thoughts??? "Dave Peterson" wrote: Just a guess, but it sounds like some of your dates are Text and some are really Dates. I'd format the date cells (all the columns) using the long date: mmmm dd, yyyy And see if there were any cells that didn't change correctly. If you find those, then fix them (reentering the date???), then you can format the way you like and try resorting. Since you're doing a lot of work, you may want to do this against a copy of your workbook/worksheet--just in case. Or make sure you close without saving if really bad things happen. Sylvia wrote: I have a spreadsheet that has many columns, some of which I am trying to sort by. One Column is called Closed Date, and is in Date Format. Another column is called Referred Date and is in Date Format. And another is called Status and is in General Format. I am sorting the spreadsheet first by Closed Date, then by Referred Date, then by Status. It sorts fine. nIf I correct a date and re-sort, it re-sorts fine. BUT, if I correct a date and a status, it does not re-sort correctly...the changed records is at the bottom of the spreadsheet out of the sort sequence. I just can't figure out what is happening and why it won't re-sort. HELP!!! -- Dave Peterson |
#5
|
|||
|
|||
I don't use Access--so take this with a grain of salt.
But I'd double check to make sure the value in the cell is a real date. (Formatting the cell as a date won't change a text value to a date value.) =isnumber(a1) will return True if A1 contains a date (or any number) (Maybe someone familiar with "analyze it with excel" can jump in.) Sylvia wrote: The dates in the Complete and Referred Columns are Date Format. They sort and and I can change a date and resort without any problem. The third Column is Status, which is a text column. The problem is when I changed the status, then try to resort, the changed line appears at the bottom, and out of sort. Just to further complicate things. This is data from an Access Query that was imported into Excel. For some reason, if the export from Access to Excel was thru "Analyze It with Excel" or exported thru a Macro, the sort problem occured. If I exported it using Export from the File Drop Down, it works fine. Any thoughts??? "Dave Peterson" wrote: Just a guess, but it sounds like some of your dates are Text and some are really Dates. I'd format the date cells (all the columns) using the long date: mmmm dd, yyyy And see if there were any cells that didn't change correctly. If you find those, then fix them (reentering the date???), then you can format the way you like and try resorting. Since you're doing a lot of work, you may want to do this against a copy of your workbook/worksheet--just in case. Or make sure you close without saving if really bad things happen. Sylvia wrote: I have a spreadsheet that has many columns, some of which I am trying to sort by. One Column is called Closed Date, and is in Date Format. Another column is called Referred Date and is in Date Format. And another is called Status and is in General Format. I am sorting the spreadsheet first by Closed Date, then by Referred Date, then by Status. It sorts fine. nIf I correct a date and re-sort, it re-sorts fine. BUT, if I correct a date and a status, it does not re-sort correctly...the changed records is at the bottom of the spreadsheet out of the sort sequence. I just can't figure out what is happening and why it won't re-sort. HELP!!! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel change default column sort to current selection | Excel Worksheet Functions | |||
How do I link two cells as to allow me to change the value in eit. | Excel Discussion (Misc queries) | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) | |||
How to change the default font and size of "comments"? | Excel Discussion (Misc queries) | |||
Why does a formula change when I sort a column | Excel Worksheet Functions |