How to auto-populate a dashboard when new sheets are added

Alicia D

I have created a workspace for my team where everyone has a sheet to enter projects and I have them all connected to a dashboard using reports and formulas. These sheets are just for small projects, but I am trying to figure out the most practical way for employees to enter their own projects and create their own sheets for their bigger projects. My question is, is there an efficient way to have their new sheets that they are creating automatically update to my team dashboard? Or will I have to go in and re-work formulas and reports every time a new sheet is created. I don't want this, as they may not want to report every project they are working on to me. Any idea or help is appreciated! Here is how I currently have it setup. Those folders are titled with my teams names and inside is one sheet and one dashboard.

Andrée Starå

@Alicia D

Yes, I did misunderstand your need.

I'd recommend having the structure look at a Report instead and then setting that Report to look at every Workspace(s) because that is the only way (without the Premium app, Control Center) to have new sheets added.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Alicia D

I hope you're well and safe!

If you reference one or multiple Workspaces, everything in them will be automatically added.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alicia D

@Andrée Starå , would you mind to go a little bit more into this? How would I reference a workspace? Right now what I have done is reference each of the sheets to create separate reports and calculations. Thank you!

Andrée Starå

@Alicia D

Happy to help!

You would select the Workspace(s) in the Report. I may have misunderstood your need.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alicia D

@Andrée Starå For instance, I created a calculations sheet to be able to create these charts. In that calculations sheet I selected "reference another sheet" to be able to come up with that. How would I do that when referencing even new sheets that pop up that I might not know about?

Alicia D

This is the formula I used to create this: =COUNTIF({Alicia DiPerri Personal Projects Range 1}, "complete") + COUNTIF({Bruce Lascola Personal Projects Range 1}, "complete") + COUNTIF({Chris Rhea Personal Projects Range 1}, "complete") + COUNTIF({Jack Frank Personal Projects Range 1}, "complete") + COUNTIF({John McGrath Personal Projects Range 1}, "complete") + COUNTIF({Julie Acosta Personal Projects Range 1}, "complete") + COUNTIF({Kevin Williams Personal Projects Range 1}, "complete") + COUNTIF({Michael Odehnal Personal Projects Range 1}, "complete") + COUNTIF({Sara Yard Personal Projects Range 1}, "complete") @Andrée Starå

Andrée Starå

@Alicia D

Yes, I did misunderstand your need.

I'd recommend having the structure look at a Report instead and then setting that Report to look at every Workspace(s) because that is the only way (without the Premium app, Control Center) to have new sheets added.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alicia D

Yes, this worked! Thank you @Andrée Starå

Alicia D

@Andrée Starå, is there a way to automatically create a conditional formatting rule for each of the new sheets that are added since we are not able to do that through the report?

Andrée Starå

@Alicia D

Excellent!

Happy to help!

Unfortunately, as far as I know, it's not possible except to change it manually.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!