I have a table in Excel which tracks details about projects (project number, name, program, etc.). I want to create a helper column to sort the project number column in this table (by the number, rather than alphabetically). I want to create this helper table in another sheet, and start by referencing the column in the table I want to sort AllData[ProjectNum]. I then copy this reference column into a row of my helper table called NumCopy so I can sort it (given the reference is immutable). The second column in the table called Order contains the formula:

=VALUE(TRIM(MID(V2, 9, LEN(V2))))

This extracts only the number part. However, when I try to sort Order by smallest to largest, nothing changes (sometimes it sorts for a second then immediately reverts to alphabetical). This column is of type number, and I have tried "Text to Columns" already. This is what my helper column looks like so far:

Any help would be much appreciated.

I have a table in Excel which tracks details about projects (project number, name, program, etc.). I want to create a helper column to sort the project number column in this table (by the number, rather than alphabetically). I want to create this helper table in another sheet, and start by referencing the column in the table I want to sort AllData[ProjectNum]. I then copy this reference column into a row of my helper table called NumCopy so I can sort it (given the reference is immutable). The second column in the table called Order contains the formula:

=VALUE(TRIM(MID(V2, 9, LEN(V2))))

This extracts only the number part. However, when I try to sort Order by smallest to largest, nothing changes (sometimes it sorts for a second then immediately reverts to alphabetical). This column is of type number, and I have tried "Text to Columns" already. This is what my helper column looks like so far:

Any help would be much appreciated.

• excel

Share Improve this question Follow edited Feb 3 at 2:44 Ken White 126k1515 gold badges236236 silver badges466466 bronze badges asked Feb 3 at 2:40 carly mcarly m 5788 bronze badges 7

• The only way this sort would fail is if you created your "helper" column outside of the actual list object table (table on the left), if your helper column is part of the actual table (table on the right) it will sort correctly. i.sstatic/CbC7TGar.png – Michal Commented Feb 3 at 3:06
• @Michal the column is definitely part of the table (when I click on it it goes into Table Design). Your screenshot looks to have the same set-up as me so I am a little confused why the sort is not working in my instance. Will continue to investigate. – carly m Commented Feb 3 at 3:13
• @Michal I have found the sorting works when I manually enter values for project number. However, when I reference these values using =AllData[ProjectNum], the sorting no longer works. Could it therefore be a problem with using values from the table? – carly m Commented Feb 3 at 3:22
• What is your Excel version? – Harun24hr Commented Feb 3 at 4:16
• @Harun24hr Version 2410 Build – carly m Commented Feb 3 at 5:38

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 0

Presumably your columns contain formulas referring to the other table. Sorting the columns does not flatten these references. If you have sheet2!a1 pointing at sheet1!a1, sorting won't move it. The formula in sheet 2 will still resolve to the same location in sheet1, which is why the sorting seems to apply for a second before recalculating.

The only practical way to do this would be to copy/paste special values those columns to replace the formulas with a copy of the data, then you can sort it. You can either sort the original data, or sort a copy of the data, you can't sort a 'view' of the data, because it's linked to the original sort.

OK so there is technically a way with vba, but it's messy and would need to be rerun everytime the data changes, so wouldn't save much time.