Dynamic Sum from Multiple Sheets in a Workspace

Hello,

I have a workspace full of our active projects. Each tasks has a status (not started, in progress, complete) and I would like be able to see the total numbers of these tasks statuses across all the projects. I already have columns that count the number of each task status in the individual projects, but I would like to be able to add or remove a project to the workspace, and have the total numbers update accordingly. Ideally I would not need to manually link/unlink and cells each time a project is added/removed from the workspace. Is this possible? I can make a report of, say, the "In Progess Count" column but I cannot use formulas in a report. Thoughts?

Find more posts tagged with

Workspaces

Sheets

Formulas

Reports

Project Management

Cell linking

Comments

Andrée Starå

Hi,

I think the best option would be to have a so-called master sheet where you link relevant data from each project, and then you can sum the different statuses there.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

TSpear

Thanks Andree,

I was hoping to avoid linking each project to a master sheet. The benefit of having a workspace for active projects is that I have reports that pull information from those projects automatically without having to micromanage linking cells, just by adding/removing a project to that workspace. I am hoping to find a way to automate these collective sums in a similar way.

Andrée Starå

Happy to help!

A possible workaround could be to have a report that adds the information automatically, and then you could export it to Excel or Google Sheets and calculate further there. It could also be sent automatically via email at specific intervals.

Would that work?

Best,

Andrée

TSpear

That may be what I have to do. I had considered exporting reports, I was just hoping there was a more automated way to gather this info. Thank you for your thoughts and help.

Andrée Starå

I'm always happy to help!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.
Formula with range data
Hello, I'm looking to see if anyone knows a formula that would add owner income and co-owner income together to reflect in Joint Income column. I'm not sure how to do this with data in ranges. Thank you.