Linking action items to project tasks


Hello everybody, first time poster. I've searched the forum to find a solution, but I wasn't successful, so decided to create my own post. I'm new to smartsheet, so would appreciate help in getting a solution for my workflow

I have a project sheet, with task names, start & finish dates, predecessors (dependencies enabled), and status column (Not started, In progress, completed). The usual stuff - Screenshot attached

I also have a separate sheet with project action items that are assigned to some users. Columns are: Action item, owner, due date, status (R/Y/G/B), complete date. Status color is auto-determined - Green if due date >  1 week in the future, yellow if due date < 1 week in the future, red if due date is today or in the past, blue if the complete date column has a date in it

Some action item examples:

  • Define vendor payment process from our company to vendor X
  • Define call flow routing for all support scenarios

I would like to link items from the action item sheet to tasks in the project sheet, such that:

  • Clear visibility on action items related to particular task
  • Update task status based on action item status:
  • If all action items under a particular task are completed, then the task status is blue
  • If all actions are green, task is green
  • If any actions are yellow, then the task is yellow
  • If any actions are red, then task is red

Note: Since we're talking about linking action items to tasks, I'm going to add a dummy action item related to each task, that way when the action is completed, the task status gets updated

I've thought about defining action items as work packages under project tasks, but then action items are numerous, and new ones keep getting added every day as we go deeper into project details. Just makes the project sheet really big, so decided to keep a separate sheet for action items

At some point, I'd also like to create a project dashboard that shows status by major categories (Go-to Market, CSP Support etc.), but that's a future post

High hopes of getting a solution. Any help is appreciated. Thanks