Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
I guess just meddling, but the benefits look dubious to me. One sheet, rather than two plus VBA, with sorting on either weight or task seems little more effort than switching back and forth between sheets and means no risk of mistakenly adding a new task to Sheet Two that belongs on Sheet One. (Though it may help to have a button to toggle the sort order.) –  pnuts Jun 19 '13 at 13:35
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

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Awesomely simple. And no second sheet needed. Thanks! –  dwwilson66 Jun 19 '13 at 13:52
    
@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. –  Matt Wolf Apr 7 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 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 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. Or any other column for that matter. Just hold down the control button and select the entire columns of A, F, and G by pushing 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. You are welcome. See how easy it is to answer a question? Next time don't offer other solutions to a problem and just solve the problem you fascists. If he wanted to sort through a table or pivot chart, he could have found that answer easily anywhere.

Sorry, I wanted to post a screen shot image of this but this website doesn't allow new users to post images. Thanks for making it harder on me webdeveloper. 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. Sorry that it looks all grouped together. This website killed all the spaces when i posted the comment. I typed it to look like columns and rows but this website wants to do whatever the F*ck it wants. I recommend you type this into an excel file. I put a bar like this "|" everywere it's a new column and an astrix like this "*" where there is a new row.

*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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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