I have a task list spreadsheet that I maintain. Each task is given a ranking based on number of days until due date, weighted by high, low, or medium priority. The ranking is automatically calculated, and the resulting list manually sorted to yield the current list of to-do's in priority order.

In a perfect world, I'd like to set up a second tab in my worksheet that would display the data from my first tab, but sort it for me on the fly. For instance, if I entered a new task that was due today with a high priority, I could flip to sheet 2 and it would already be automagically sorted to the top of the list.

Sheet One (as entered; number is calculated weight)

Task 01, 06/20/2013, Low,  0009
Task 02, 06/20/2013, High, 0999
Task 03, 06/30/2013, Med,  0089
Task 04, 06/19/2013, High, 1000  <-- new entry

Sheet Two (automatically sorted, descending order on calculated weight)

Task 04, 06/19/2013, High, 1000 
Task 02, 06/20/2013, High, 0999
Task 03, 06/30/2013, Med,  0089
Task 01, 06/20/2013, Low,  0009

We're transitioning from 2007 to 2010 in our organization, so I want to make sure that any soutions work in both versions. Any ideas?

  • 1
    If this info is in a table just use the filter dropdown to sort by your weighting field, once that's been done once you just just hit the dropdown to filter and hit done, which would be a lot less work than creating a whole second sheet, and is far more reliable – CLockeWork Jun 19 '13 at 13:45

Excel does this sort of thing easily. You shouldn't even need a second sheet (although you could certainly use one).

  1. Convert your list of tasks to a Table Insert>Tables>Table.
  2. In your Calculated Weight Column, use the drop down arrow and select Sort Largest to Smallest.
  3. Each time you add a row to your table, just re-sort and you'll always have the highest priority first.

Here's what the Table and associated Pivot would look like. You could place them on the same or separate sheets, to your preference.

Table and Pivot

As an alternative, you could have this table feed a Pivot Table which would always be sorted in the same manner, but you'll still have to refresh the Pivot Table whenever you update the original data Table (whether manually or automatically based upon a time period or workbook open). This functionality is available in both 2007 and 2010.

  • Awesomely simple. And no second sheet needed. Thanks! – dwwilson66 Jun 19 '13 at 13:52
  • 1
    @dav, is there a way to re-sort automatically? I have automatically updating cells and depending on incoming values the rankings change. I am looking for a way to have the table resort automatically (similar to conditional formatting) without having to click the re-sort button. – Matthias Wolf Apr 7 '14 at 7:47
  • Only two ways that I know of are 1) if the data is coming from another source (e.g. SQL query), and the source data is sorted, the table should update when the data is refreshed or 2) VBA, which could be set to update when data is added, the file is opened, or by another command (e.g. keyboard shortcut). – dav Apr 8 '14 at 12:15
  • And, there is a built-in keyboard shortcut for reapplying a custom filter/sort: CTRL+ALT+L. In fact, the mini-dialog box says that new or modified data won't be filtered or sorted until you click Reapply. – dav Apr 8 '14 at 12:21

This is how you sort data dynamically. The first one shows the results while the second one shows the equation used. Don't worry about the "C" Column being a bunch of random numbers. That's just the date converter that you don't have to understand. It's like computer formula for the date or something. Anyways the VLookups are all pretty much the same so it's easy. Rank column needs to be the far most left column. Since you put 0009 and all those other crazy numbers, I had to write them in as text rather than numbers. Because of this, I couldn't use rank to order them. So I created column "F" to convert the text 0009 to just 9 in number format. I used the Value function to do that. That pretty much covers it. Now you can hide columns A, F, and G if you don't want to see them. Just hold down the control button and select the entire columns of A, F, and G by clicking on the actual letters A, F, and G on the column labels. Then just right click one of those columns and find where it says hide.

Sorry, I wanted to post a screen shot image of this but this website doesn't allow new users to post images. Here is the output table. I skipped the other columns because they would take up too much space on this website and they are almost exactly the same as columns G and H, so it shouldn't be that hard for you to figure it out.

   A|       B|         C|    D|      E|     F|       G|       H|         I|
Rank|   Tasks|      Date|  H-L| w-text| w-num| FinRank| VLookup|   VLookup|
   4| Task 01| 6/19/2013|  Low|   0009|     9|       1| Task 04| 6/19/2013|
   2| Task 02| 6/20/2013| High|   0999|   999|       2| Task 02| 6/20/2013|
   3| Task 03| 6/30/2013|  Med|   0089|    89|       3| Task 03| 6/30/2013|
   1| Task 04| 6/19/2013| High|   1000|  1000|       4| Task 01| 6/19/2013|

Here is the formulas

Column A, where it says 4: =RANK(F2,$F$2:$F$5,0)
Column A, where it says 2: =RANK(F3,$F$2:$F$5,0) and so on and so forth...
Columns B, C, D, and E are just text you input.
Column F, where the 9 is: =VALUE(E2)
Column F, where the 999 is: =VALUE(E3) and so on and so forth
Column G, is just numbers from 1 to whatever number you want. You just type them in. You need this to do the VLookup.
Column H, first row: =VLOOKUP(G2,$A$2:$F$5,2,FALSE)
Column H, second row: =VLOOKUP(G3,$A$2:$F$5,2,FALSE) and so on and so forth...

The rest of the columns are just like column H.
Column I row 1 looks like this: =VLOOKUP(G2,$A$2:$F$5,3,FALSE)
Column J row 1 looks like this: =VLOOKUP(G2,$A$2:$F$5,4,FALSE)
Column K row 1 looks like this: =VLOOKUP(G2,$A$2:$F$5,5,FALSE)
*See how there is only 1 difference between them? Easy right?

  • 1
    Welcome to superuser, WarPiglet! Note, that you should watch your words a little and try not to offend other users. Also, please make sure to make yourself familiar with the formatting options available in the superuser markdown editor. As you see, it is possible to format the table the way you intended. (Also note, that 0009 must not necessarily be a "crazy number" but could simply be the value 9 with the cell's number formatting set to 0000.) – Marcus Mangelsdorf Nov 3 '15 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.