Legal projects/task management sheet

Tomer

Hi, 

I’m part of an in-house legal department. The company has different departments and different types of contracts/advice they require us to produce.

To track the work, I have created one sheet for the legal team to use, covering all main "legal work" categories and then broken down to sub categories, tasks and then sub tasks underneath (see snapshot attached).

The majority of the tasks are different contracts we initiate, negotiate, process and follow until signing.

Pros:

1. All information is in one place (breaking it down to different department would result in too many sheets), gives the team/manager an overview of what’s happening
2. Easier management of resources for the manager

Cons:

1. Sheet now very big, hard to find the right category/sub category
2. Very manual; little automation; hard to add a new task
3. can’t really use the functionality of forms as system doesn’t know into which category to add a new task to
4. Searching and finding is hard; filtering not much help either.
5. I guess the 'smart' of Smartsheet is not fully utilized.

My questions/request:

1. can someone please share a good template to manage many different tasks/projects, split into categories which may be a better solution than the above?
2. Can someone suggest way to simplify and overcome the cons mentioned above?

Many thanks for your help!

Tomer

Charles DESCOTES

How about adding a new column called "categories", another colum could be called "sub-categories" if needed.

Then you can filter on these columns to fetch the sub-group you need.

Reports can do exactly that ... on several smartsheets !

Therefore you can multiply the sheets for ease of work, but consolidate with the reports when you need.

https://help.smartsheet.com/articles/522214-creating-reports

Tomer

Thank you Charles, 

So you recommend to move away from the format Iv'e used so far and create a sheet where all tasks appear as rows? and categories as columns?

some of the tasks are complex, so require sub tasks. wouldn't that look messy on the sheet?

still hoping that someone can share a template 

confused...

thanks

Tomer

Paul Newcome

You could add a column (we'll call it "Level" here) and use =COUNT(ANCESTORS())

 

You could then adjust your hierarchies as follows:

 

- Agreements                                             0 (These numbers are your "Level")

       - Terms                                               1

               - Happy Supplier                         2

                         Sub 1                                3

                         Sub 2                                3

               - Another Happy Supplier           2

                         Sub 1                                3

                         Sub 2                                3

       - More Terms                                      1

               - Supplier                                    2

                         Sub 1                                3

                         Sub 2                                3

               - Another Supplier                      2

                         Sub 1                                3

                         Sub 2                                3

- Disagreements                                        0 

       - Even More Terms                             1

               - Unhappy Supplier                     2

                         Sub 1                                3

                         Sub 2                                3

 

Drag fill the formula down to the bottom row (will auto-populate when new rows are added). You can then search and/or filter based off of the value in your Level column. As you can see... All of the various levels of hierarchy will have the same number based on its level.

 

I do believe what Charles means is to create 1 additional column and enter the category for each row in that one column which is also a good idea.

 

If you want to keep the sheet looking clean, you can shove the new column all the way over to the right side of the sheet, right click the column header, and select "Hide Column".

 

You could also use a different sheet for each category. While it may seem like a lot of sheets, you can then create a separate form for each category linked to the appropriate sheet which would help with your automation issues. Then you can create a report based on whatever criteria you want.

 

If you really wanted to get into it, you could pull multiple reports, one for each Status or however you want to break it down, and have those reports displayed on a dashboard with the "Interaction" set so that the manager can simply click on the report on the dashboard and be taken straight to the source report. Throw in an extra sheet that has a formula keeping a live count of each Status (or whatever you want) and have that displayed as a graph on the dashboard as an overall view of how well (or not so well) the business is doing.

 

Below is a screenshot of my department's dashboard. It has a list of quick links, a reminder to everyone, each person's incomplete tasks, lists of each client currently in the implementation phase broken down by status, as well as a pie chart that shows an overall view of what is going on in a comparative way. 

 

P.S.

The dashboard is still "under construction" so don't mind any typos. I am also not much of an artistic guy, so don't mind the wobbly lines. Haha.

 

I hope we've been able to help.

Charles DESCOTES

Hi Tomer,

Yes I suggest that each time you need a filtering or sorting axis (Priority, Status, Lead / Whom, Category, Country, Company, Dpt, Strategic ranking, Concern ranking, Delay/Committed date, Investments, Turn over, Etc.etc.) create a dedicated column.

Content of this column can be either logged in or calculated (one 'smart' aspect of SmSh being the mix of planning and calculating.)

Using the function =PARENT() is a simple way to duplicate the parent (=header) data down to children rows and therefore enabling filter to show children too.

For instance you could do that in you Priority column if you want to see the children rows when you select High Priority project (expanding / collapsing rows will still work).

Remember there are several ways to easily duplicate a formula to cells underneath (drag bottom right corner; use Ctrl D; or simply Copy/Paste; Ctrl C / Ctrl V).

Basically, it is turning your SmSh into an usable DataBase.

It will also enable the Card View possibilities, great for overviewing.

 

Re. the messy look,

Yes indeed, your sheet(s) will be a bit more complicated, so as put by Paul: locate the new columns right to the right end (but do not forget to address, so I would not hide them).

For the sake of read-ability, children could have the information in a light grey font for instance. Unfortunately conditional formatting (as far as I know) is not sophisticated enough yet to do this automatically ... without adding more column!

 

Paul Newcome

Yes indeed, your sheet(s) will be a bit more complicated, so as put by Paul: locate the new columns right to the right end (but do not forget to address, so I would not hide them).

For the sake of read-ability, children could have the information in a light grey font for instance. Unfortunately conditional formatting (as far as I know) is not sophisticated enough yet to do this automatically ... without adding more column!

 

I love automation, so I generally have quite a few "helper columns". I have been tasked with building numerous sheets containing a lot of different information for some of my much busier coworkers. The more automation I use, the easier it is for them to use the sheet. 

 

I use formulas to generate the criteria within those  helper columns. That way it will auto-fill when new rows are added or old rows are moved around. I then use conditional formatting based on those helper columns to drive parent row formats that will automatically change depending on the row's level of hierarchy if I happen to indent or outdent anything or highlight specific cells based on dates and whatnot.

 

Only AFTER I get that automation set up and everything running smoothly do I move those columns over and hide them since they are now automated based on formulas and no longer need edited or referenced.

 

Whenever I need to edit the layout of a sheet I will then unhide them and change the font color to red (stands out quickly), change what I need to change and make sure the automation is still working, and then hide them again before allowing access to my coworkers. If they can see it, they wonder what it does. If they wonder, they try to edit. Then accidentally hit the save button and can't undo. Then come back and ask me to fix something they didn't need to be messing with in the first place.

 

I personally go total overkill when hiding columns, but I'd rather be safe than sorry. I shove them all the way over to the right, shrink the column width to minimum, change font and background colors both to white (makes them look empty), lock the columns, then hide the columns. I know I don't need that much, but I live by Murphy's Law when it comes to complex things.

 

That's just my personal preference though. Haha

Tomer

good stuff both, thank you very much. 

It's all seems wizard level and very useful, but I haven't reached that level of SmSh understanding yet nor have lots of experience with using formulas. 

picking up the points I've understood and taking it forward from there I guess. 

Thanks, 

Tomer

 

Andrée Starå

Hi Tomer,

Another idea to throw in with all the great suggestions already would be to utilize the Card Views Level function. With that, you could choose which level you want to see off the sheet. E.g., You have a sheet that the negotiation phase is always on level 4. You could then chose to see the level 4 information.

More info: https://help.smartsheet.com/articles/2302238-using-card-view-to-visualize-your-project

Would that work for you?

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

Paul Newcome

Card view is still something I'm learning. I have been able to make everything work that I need thus far without it, so I haven't had much reason to play with it and therefore always forget about it as an option. Definitely a good suggestion.

Tomer

thanks

Andrée Starå

Happy to help!

jalubro

Hi Tomer

i know this is now a fairly old thread, but since we have developed several matter type templates for CLD’s to adopt I thought I’d make you aware. We are a Smartsheet partner specialising in Legal. Happy to share some ideas 

Lisa Luetkemeyer

I am a new Smartsheets user and am trying to develop some templates as well. Are there any legal-specific templates available via the Smartsheets solutions center? I have not found any thus far.

Andrée Starå

Hi Lisa,

Here's a template for Legal Case Management.

https://www.smartsheet.com/marketplace/templates/legal-case-management

Would that work/help?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!