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.

Capture.JPG

Comments

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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å
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

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

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    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. 

    thinkspi.com