Reference another sheet using sheet name

I'm almost certain that there is no solution to this situation, however, here goes. I have a workspace with approximately 35 project sheets. Each project has approximately 50 tasks and each has a schedule status in a "Schedule" column. I want to be able to count these tasks based on their stats and report on the entire portfolio on a dashboard.

I could create reports based on the schedule status, but I can't calculate anything on a report.

I could create a schedule summary row on each sheet and input countifs formulas in that row, but there is no way to compile all projects into 1.

I could create a sheet with a row for each project and a column for each (of 4) stats types then manually create 70 named references (2 for each countifs formula) and manually update this each time a project is added or removed. Then sum the columns and reference that summary column to the dashboard.

This seems to be the only way that will work, but is extremely cumbersome. I'm not sure if the sheet would function well with over 70 named references.
- To make this less cumbersome I was wondering if it was possible to use the sheet and column name in a formula to reference another sheet. See attachment.

Any solutions out there?

Thanks in advance.

Find more posts tagged with

Sheets

Dashboards

Project Management

Cell linking

Comments

Andrée Starå

Hi Jacob,

Not sure if this is one of your options.

You could create a summary row or section on each sheet, and then cell link them to a master sheet and do more calculations on the information there.

Would that work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Jacob Slaathaug

That would be a solution combining a couple options I've mentioned, however, I would still have to manually create Named references for each sheet and each new project. I'm hoping to find some sort of automation.

Andrée Starå

You wouldn't need to do any named references because you would use cell links (not cross sheet links) instead.

Would that work?

Best,

Andrée

Jacob Slaathaug

I would have to manually create the cell links instead of manually creating named references correct?

Andrée Starå

That's correct, but I think it will probably be the easiest solution.

Best,

Andrée

Paul Newcome

I agree with Andree. In this particular case, it seems like using cell linking would be the easiest way to go to pull all of the required data onto one sheet and then run your totals from there.

Sign in to join the conversation!

Quick Links

Trending discussions

Mr
hi would like to create a simple patient tracking system for our small clinic where i can record the time & duration a patient appeared in our establishment. Using a central sheet where it records all the data to be accessible by the admin. There will also be Four (4) forms to be accessible by the three department. Concept…
Forms
On the App - "In Smartsheet forms, is there a way to capture or upload an image directly into a specific column cell (per row) without using the Attachments field? I need each section of the form to have its own dedicated image, not just general attachments for the row."
Smartsheet Mobilizer Kit2/Best Practices
Hi - I am launching Smartsheet sessions at my organization for the next 3 months. The SS Mobilizer Kit2 Workspace I saved a few years back, the files are not loading. Is there a new resource kit? Also, I am writing up some best practices for our users: Examples: Names, separate first and last name into two columns. Email…