How to create multi-level collapsible sortable to-do list/task list

[ajkessel]

I've looked at a number of "to-do list' templates and have not found
any that provide what I'm looking for. I'm wondering if it's possible.

I'd like to have a task list that tracks items for several different
projects. Column headers would be something like: project, start date,
end date, description, priority, complete. Ideally I would also have
the option to have one or more levels of subtasks for each task.

I'd like the task list to be collapsible -- so at the highest level, I
would just see a list of projects, or I could drill down to the second-
level subtasks.

I'd also like to be able to sort between projects so I can see the
most urgent or nearest due tasks regardless of project. (The "to-do
list for projects" template on office.microsoft.com tracks projects
separately so it can't be sorted this way).

The part I'm most stuck on is the collapsing part -- I thought there
should be some way to do this using grouping/outlining but however I
attempt seems pretty awkward. First, for auto-outlining to work, I
think I need to use sum() which doesn't make sense in a task list
(although I can fill the cells and make them white on white). More
importantly, though, once cells are grouped, they are no longer
sortable across projects by fields like due date (the groups stay
together). If I ungroup them and then sort, the task headers that
would be used for collapsing get out of order.

Any suggestions for how to approach this? Is it just too much to ask
of Excel to do this in a convenient way?

 

[Niek Otten]

I don't think you should use Excel for this.
Use an outline processor or use Word's (limited) outline processing capabilities.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I've looked at a number of "to-do list' templates and have not found
| any that provide what I'm looking for. I'm wondering if it's possible.
|
| I'd like to have a task list that tracks items for several different
| projects. Column headers would be something like: project, start date,
| end date, description, priority, complete. Ideally I would also have
| the option to have one or more levels of subtasks for each task.
|
| I'd like the task list to be collapsible -- so at the highest level, I
| would just see a list of projects, or I could drill down to the second-
| level subtasks.
|
| I'd also like to be able to sort between projects so I can see the
| most urgent or nearest due tasks regardless of project. (The "to-do
| list for projects" template on office.microsoft.com tracks projects
| separately so it can't be sorted this way).
|
| The part I'm most stuck on is the collapsing part -- I thought there
| should be some way to do this using grouping/outlining but however I
| attempt seems pretty awkward. First, for auto-outlining to work, I
| think I need to use sum() which doesn't make sense in a task list
| (although I can fill the cells and make them white on white). More
| importantly, though, once cells are grouped, they are no longer
| sortable across projects by fields like due date (the groups stay
| together). If I ungroup them and then sort, the task headers that
| would be used for collapsing get out of order.
|
| Any suggestions for how to approach this? Is it just too much to ask
| of Excel to do this in a convenient way?

 

[ajkessel]

Unless I'm mistaken, I don't think I can get the cross-project date
sorting feature with Word -- can I?

 

[Niek Otten]

I don't know. I said that Word has limited outline capabilities. But more than Excel.

Look for a "real" outline processor.
I don't know too much about this. I used MORE! 15 or maybe 20 years ago.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


Unless I'm mistaken, I don't think I can get the cross-project date
sorting feature with Word -- can I?

 

[Rick Rothstein \(MVP - VB\)]

I think what you are describing will need to make use of the VBA coding
facilities built into Excel; however, like any complex project (your
ultimate project seems to me like it might be complex), your layout and
operating rules should be laid out and designed before any coding starts.
Plus, of course, you will need someone conversant with the Excel model and
VBA to code it. Just to tantalize you a little bit, go to an empty worksheet
in an Excel workbook and type some "pretend" project names scattered about
in Column A (leave empty cells between them). Next, on the row containing
the first header, put this formula in Column B...

=CHAR(64+ROW(A1))

copy it across for awhile (doesn't have to be much, I just want you to have
something to look at), then copy the selection that results from your
"copying across" down to somewhere past the last project name. Now,
right-click the worksheet tab at the bottom of the worksheet and select View
Code from the popup menu that appears. When you do this, the VBA editor will
open up and display the code window for the worksheet whose tab you
right-clicked... copy/paste the code below my signature into that code
window. Now go back to worksheet and double-click a project name. The items
under it should collapse. Double-click the project name again and the items
under it should reappear. Do this for any of the project names (you do
**not** have to "un-collapse" another project name's collapsed items before
collapsing other project names... you can collapse and un-collapse in any
order).

Rick

'***************** START OF CODE *****************
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim NextHeaderRow As Long
If Target.Column = 1 And Target.Value <> "" And _
Target.Row <> MaxRowInUse Then
NextHeaderRow = Target.End(xlDown).Row
If NextHeaderRow = Rows.Count Then NextHeaderRow = MaxRowInUse + 1
Range(Target.Offset(1), Rows(NextHeaderRow - 1)).EntireRow.Hidden = _
Not Range(Target.Offset(1), Rows(NextHeaderRow - 1)).EntireRow.Hidden
End If
Cancel = True
End Sub

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = True) As Long
Dim X As Long
Dim LastRow As Long
Dim Col As Variant
Dim AddressParts() As String
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For Each Col In .UsedRange.Columns
On Error Resume Next
If FactorInHiddenRows Then
AddressParts = Split(UsedRange.Address, "$")
For X = AddressParts(UBound(AddressParts)) To 1 Step -1
If .Cells(X, Col.Column).Value <> "" Then
LastRow = X
Exit For
End If
Next
Else
LastRow = .Cells(.Rows.Count, Col.Column).End(xlUp).Row
End If
If LastRow > MaxRowInUse Then MaxRowInUse = LastRow
Next
End With
End Function
'***************** END OF CODE *****************

 

[ajkessel]

Thanks, that's definitely a good start. I have some VBA experience and
can probably figure out what I need. It sounds like basically your
suggestion is that the "grouping" functionality isn't going to cut it
here.

 

[Rick Rothstein \(MVP - VB\)]

I've never used Excel's "grouping" functionality, so I can't say for sure. I
skimmed the help files on Outlining (what I think you are referring to when
you say "grouping") and it looks like what you want may be possible, but
there seems to be a lot of set up work associated with it to me (again, that
is the view from someone who has never used it before). I'm basically (no
pun intended) a coder, so I tend to gravitate to coded solutions to things
that look like they have some level of complexity associated with them.

Rick


Thanks, that's definitely a good start. I have some VBA experience and
can probably figure out what I need. It sounds like basically your
suggestion is that the "grouping" functionality isn't going to cut it
here.

 

[ajkessel]

Here's a start using the grouping/outlining functionality (I call it
"grouping" because the Excel 2007 Ribbon Button is "Group"). This
snippet makes a collapsible outline, basing the titles for each group
on a blank cell in column 2:

For Each X In rng.Rows
If X.Cells(1,2),Value = "" Then
X.OutlineLevel = 1
Else
X.OutlineLevel = 2
End If
Next X

The outline can then be expanded and collapsed using the standard
buttons. I plan to expand it to allow multiple levels.