Task totals across multiple plans & within a set timeframe

melissalk

Hello!
My formula skills are pretty basic so I feel like this might be a bit more than that.

I am trying to calculate the number of tasks for four departments across multiple project plan sheets. I want to look at a timeframe scale of the last 3 months and the next 6 months.

The four departments are: Editorial, Design, Production, Quality-Copyedit.

My end goal is to show this as a line graph on a dashboard so we can see peak times for each department during the timeframe. I started to create a metrics sheet to set up references and pull the data but I'm stuck on the figuring out the formula to look at that date range.

The image below is from our project plan template:

And the graph that started this whole idea is from the Data Input & Data Collection template set. This is what we're trying to accomplish but not an average; we want actual task count. I've tried using the sheet this pulls from as a guide, but I'm not having the best luck.

Any help is much appreciated! :-)

Thank you,
Melissa

jmyzk_cloudsmart_jp

Hi @melissalk

You must create multiple metrics sheets to use the same formula structure, each summing task numbers of corresponding Project Tasks sheets, as the range references like {Task Name}, {Task Type}, and {Actual Start Date} refer to a specific sheet, if you are using column formula, which is more robust. (Alternatively, you can use the cell formula to get the task numbers in a sheet, as shown below.)

https://app.smartsheet.com/b/publish?EQBCT=cb04f3a035454987b15dbb2a861ff347

(It is a good custom to use {sheet name: column name} combination like, {Sheet1: Task Name}, {Sheet 2: Task Name)

In my copied demo dashboard, I used the first method.

I created a folder to hold the project task sheet and metric sheet. Then, copied the folder to create sheets with the same formula and cross-sheet reference structure.

Then, I created a report to summarize the two metric sheets.

https://app.smartsheet.com/b/publish?EQBCT=b07525a124714d3e8c5ccf7aadcdd1d7

The report's data is shown at the bottom in the demo dashboard.

https://app.smartsheet.com/b/publish?EQBCT=4d1d96d1ffcb48c08bf06ab35c1abe33

If you are going to start creating multiple sheets for multiple projects, we recommend using the method just described. This is because it is time-consuming to create cross-sheet references.
If you already have multiple sheets in your project, you will need to manually create references for the number of sheets multiplied by the number of references. This is the same in the case of a single metric sheet or multiple sheets combined in a report, but multiple sheets are easier to manage.