How to Create a Helper Sheet that will be referenced by Project Management Sheets?

Options

Hi everyone, kind of new to SmartSheet and I've been asked to create a platform for project management using Smartsheet since we already have and are invested in it. What I'd like to do is to create a helper or reference sheet that all future projects would be linked from. This way, if a row is added to one of the phases in our basic project structure, it will use reference lookup links to automatically add said new row to ALL projects made from that original template.

In short, what I'd like is entire columns to update in real time across multiple projects by making on change on the helper sheet.

I tried doing cross sheet reference column, but when i add a row, and then update the reference, I'm not seeing my new content where it is supposed to be.

I'm struggling with this one a little, but I've tried VLOOKUP and INDEX MATCH but the way they're structured doesn't seem to be autofilling whole columns across sheets.

What we are trying to do is not have our project admin have to update 50 existing sheets with a new row manually.

What's the best way to go about doing this?

Thanks Everyone!

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 10/05/23
    Options

    If you're only managing 5 or 10 projects or less, then update everything manually.

    Anything more than 5 or 10 projects, and you might consider Control Center where you can update all projects from one blueprint. You can't however use your existing project plans and implement Control Center over it. You have to start your templates from your Control Center Blueprint.

    I will caveat that with adding 3 lines to 50 project plans wouldn't take very long, but doing that often would be annoying.

    Otherwise without Control Center, you would need multiple helper columns (probably hidden ones) in every project plan with complex formulas and automation built in to accomplish what you're after.

    Your ask is a huge task. Rolling up items from one/many grid(s) to another is something many seek to do, but quickly learn it's not as easy as one may think.

    The formulas you mentioned (vlookup and index match) will be your friend (index match more so). Them along with some unique ID's will go a long way. By the way INDEX MATCH is too separate formulas used together.

    Best of luck to you!

    -Ray

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!