Changing priorities in a column

**phrankndonna** · 10-14-2013, 11:00 AM

Hi,

I’ve got a task list in column A and a priority list in column B. When I finish one of the tasks, I put ‘Done’ in column B next to the task. I’m looking for the best way to reprioritize the rest of the column (i.e., when I finish the priority 1 task, priority 2 becomes Priority 1, priority 3 becomes priority 2, etc.). The column will obviously have numbers and the word ‘Done’ in cells, so there are two different data types. I’m thinking the best way would be with VBA, but there may be an easier way with a helper column with formulas. I appreciate any help and guidance. Thanks.

Frank

**newdoverman** · 10-14-2013, 02:12 PM

Try this small example and see if it does what you want.

**phrankndonna** · 10-15-2013, 10:06 AM

Perfect! I didn't think of using a second column for the status. Doing that and then sorting also moves the completed items to the bottom of the list. What I will do is use this and also add a vba macro that automatically sorts the sheet by priority upon change. I just realized, though, that I will also need to work out re-prioritizing when I add a new task that doesn't have a priority at the end (i.e., new priority because pri 3, the current pri 3 becomes pri 4, etc.). Unless you know how the formula would work, I'll play with it by adding 'new' or something like that to the status column. Thank you!

**newdoverman** · 10-15-2013, 11:31 AM

The priority is set by the row that the task is in via formula in the priority column. If you want to insert a new task with say a priority 2, right click on the item with priority 3 and choose INSER then Rows Above. This will insert a new row but it doesn't have a priority. Click on the cell above the empty Priority cell to select the cell above. With the mouse, grab the selection handle in the lower right of the cell and drag down to the vacant cell. The formula for the priority will be copied and all the other priorities under will be appropriately incremented.

**phrankndonna** · 10-19-2013, 10:44 AM

Hi again. I wanted to follow up with you with the finished product. I took your suggestion and combined it with a VBA macro on the worksheet that automatically sorts and reprioritized the sheet if the word 'Done' is entered. This also moves all the completed tasks to the bottom. Nice! I also added a button that runs a macro to insert a line and copy the formulas. You just put your cursor where you want the new task to assume that priority and click the button. A line is inserted, formulas copied, and the cursor is moved to column A so you can enter your new task.

The attachment is a macro-enabled workbook, and I know people are wary of downloading those (I know I am). But it's just your workbook you attached with the two small macros I mentioned.

Thank you very much for your help with this!

Frank