Reference another sheet using sheet name

Jacob Slaathaug
edited 12/09/19 in Smartsheet Basics

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.