Reference another sheet using sheet name

Jacob Slaathaug

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.

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.