Link a summary sheet to multiple workplans

Lexington ✭✭
edited 12/09/19 in Formulas and Functions

I'd like to be able to link to fields in a separate worksheet based on certain criteria. I'll give an example to try and explain:

I have multiple projects running concurrently; each has a separate project plan as they are shared with different stakeholders. Each project plan has an overall RYGB status, based on the 'worst' status of the project (ie 5 green and 1 red item = a red project), and this overall status is in the same cell of each project plan. 

On the summary sheet, I'd like to be able to give this status next to each project name, looking like

  • Project 1:  G
  • Project 2:  R
  • Project 3:  G

Rather than manually using 'link from cell in other sheet' each time I add a new project, is there a way to do some kind of lookup to take the name of the project, then link to a specific cell (eg [status]1) in the sheet with that project name. 

Is this just wishful thinking? I'm fairly new to smartsheet but I've gone through the community and help pages and can't find anything.

Thanks in advance!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!