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.